How can I get postgresql local configuration parameter's current value?

Question

I'm practicing with queries optimization (Postgres 13). In particular, tuning work_mem parameter.

There's the DB diagram: https://postgrespro.com/docs/postgrespro/12/apks02.

I have a sql function get_passengers_and_flights:

CREATE FUNCTION get_passengers_and_flights(d timestamptz)
RETURNS TABLE(passenger_name text, flight_no text)
AS $$
  SELECT t.passenger_name, f.flight_no
  FROM tickets t
    JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
    JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.scheduled_departure >= date_trunc('month', d)
    AND f.scheduled_departure  < date_trunc('month', d) + interval '1 month'
  ORDER BY f.scheduled_departure, t.passenger_name;
$$ LANGUAGE sql;

And a simple query:

SELECT * FROM get_passengers_and_flights('2017-06-01') LIMIT 3;

By default workmem = '4MB', so sorting takes some time. One of the ways to make it better to increase work_mem from '4MB' to '32MB' or more. In the latest versions of postgresql we can do it localy (for function, table and etc.):

ALTER FUNCTION get_passengers_and_flights SET work_mem = '32MB';

So, quicksort was chosen and execution time decreased.

But the question is how can I see work_mem local value for get_passengers_and_flights function?

Obvious way to get work_mem's value is:

SELECT current_setting('work_mem');

The output is global value '4MB', so how can I get local '32MB'? I skimmed through system catalogs description and got nothing.

I'm not sure how to do exactly what you want, but... To force it, rather than set it and check it per connection, edit the value in /usr/local/var/postgres/postgresql.conf and restart the postgres server. Just be careful as this is meant to avoid starving processes. I believe total RAM = work_mem * concurrent connections. work_mem is assigned per connection. I'm not sure exactly how to check for starving processes, but something to learn. — JustBeingHelpful, Apr 29 '23 at 21:18
The question is: see the local value **where**? It's simply visible inside the function body. Or do really want to see properties of the function? (The `CREATE FUNCTION` statement, basically.) Also, in my hands, on Postgres 15, that query uses an `Incremental Sort` with such a small `LIMIT`. You may be barking up the wrong tree to start with, but there is not enough information to make this a proper *performance* question. See: https://stackoverflow.com/tags/postgresql/info — Erwin Brandstetter, Apr 29 '23 at 23:04
I see. I have Postgres 13. At first it gets the whole table as a function result and then fitering it. So I've got no incremetal sort in my case, cos Postgres 13 just can't look into function's result before the very end. Probably it was fixed in Postgres 15... — Alex, Apr 30 '23 at 06:28
To be honest, it's not a performance question. I just gave a problem's context. It's more about setting up Postgres. Anyway, thanks for your opinion, I'll try to be more accurate next time. — Alex, Apr 30 '23 at 06:40
Before changing anything in the configuration, I would start with the query plan, using EXPLAIN(ANALYZE, VERBOSE, BUFFERS). With some proper indexing, this should be lightning fast, even with a small memory setting for work_mem. — Frank Heikens, Apr 30 '23 at 08:21
By the way, the biggest issue is LIMIT 3 outside of the function. — Frank Heikens, Apr 30 '23 at 09:21
You won't get execution plan for query inside function using explain. Auto_explain is a way. As for the optimization, I just play with local parameters, that's the point) I don't state that it's the best way to optimize the query. — Alex, Apr 30 '23 at 15:51

JohnH · Accepted Answer · 2023-04-30T07:07:20.880

2

The following query should give the desired results:

SELECT
  REGEXP_SUBSTR(c.setting, '[^=]+$') AS work_mem
FROM
  pg_proc
  CROSS JOIN unnest(pg_proc.proconfig) c(setting)
WHERE
  oid = 'public.get_passengers_and_flights(timestamptz)'::regprocedure
  AND c.setting LIKE 'work_mem=%';

NULL is returned if work_mem has not been locally configured for the function.

edited Apr 30 '23 at 07:07

answered Apr 29 '23 at 21:35

JohnH

2,001
1
2
13

2

I've revised my answer to address overloaded functions by using `regprocedure` to guarantee that only the intended function's setting is returned. Thanks to Erwin Brandstetter for mentioning this. – JohnH Apr 30 '23 at 07:13

score 0 · Answer 2 · answered Apr 29 '23 at 23:14

If all you want is to see properties of the created function, use the dedicated system information function pg_get_functiondef():

SELECT pg_get_functiondef('public.get_passengers_and_flights(timestamptz)'::regprocedure);

This gets the function OID reliably - assuming it's created in schema public:

'public.get_passengers_and_flights(timestamptz)'::regprocedure

See:

How can I get postgresql local configuration parameter's current value?

2 Answers2