Postgres: (how) can I list only querying-relevant settings from `EXPLAIN (SETTINGS true)`?

Question

Non-default configs can be listed with

SELECT name, setting, source, sourcefile FROM pg_settings WHERE source !='default';

But (how) can these configs be further narrowed down, so the list would be equivalent to what EXPLAIN(..., SETTINGS true) returns? F.e. even if track_activity_query_size has been changed, EXPLAIN (SETTINGS) doesn't return it (and rightly so). One could just manually filter out the about-100 configurations, but I'm wondering whether there's an elegant way to match the EXPLAIN's output.

Answer 1

Try the given command to get a list of only the configuration options that apply to query planning and execution and match what EXPLAIN (options true) returns:

SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE source != 'default'
  AND source IN ('session', 'user');

Only settings that have been changed at the session or user level are included in the pg_settings query's results. When you run EXPLAIN (options true) for a particular query, these options are  considered.

Hope it works :)