EXPLAIN (ANALYZE, SETTINGS) display wrong tablespace parameters

Question

PostgreSQL 12.8

I have a tablespace called mattermost and a database mattermost-dev in it.

postgres=# \l+ mattermost-dev
                                                 List of databases
      Name      |   Owner    | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
----------------+------------+----------+-------------+-------------+-------------------+-------+------------+-------------
 mattermost-dev | mattermost | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 28 MB | mattermost |

I have set parameters for this tablespace:

postgres=# SELECT * FROM pg_tablespace where spcname = 'mattermost';
   oid    |  spcname   | spcowner | spcacl |                     spcoptions
----------+------------+----------+--------+-----------------------------------------------------
 39843057 | mattermost |       10 |        | {random_page_cost=1.1,effective_io_concurrency=200}

But when I execute any query in database mattermost-dev, EXPLAIN shows me effective_io_concurrency = 2 - that is the value for the default tablespace. And it does not show the random_page_cost parameter, because for the default tablespace it is set to the default value 4.

mattermost-dev=# explain (ANALYZE,  SETTINGS) select * from teammembers where userid = '3ya1arysntboz8w3in5qbny11y';
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on teammembers  (cost=1.39..3.53 rows=2 width=66) (actual time=0.088..0.089 rows=2 loops=1)
   Recheck Cond: ((userid)::text = '3ya1arysntboz8w3in5qbny11y'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_teammembers_user_id  (cost=0.00..1.39 rows=2 width=0) (actual time=0.057..0.058 rows=2 loops=1)
         Index Cond: ((userid)::text = '3ya1arysntboz8w3in5qbny11y'::text)
 Settings: effective_cache_size = '32256MB', effective_io_concurrency = '2', max_parallel_workers = '6', max_parallel_workers_per_gather = '3', work_mem = '2752kB'
 Planning Time: 6.430 ms
 Execution Time: 0.178 ms

Why does EXPLAIN ANALYZE in the database in a non-default tablespace display the parameters for the default tablespace? I did not restart the instance after I set the parameters for mattermost tablespace, but only execute pg_reload_conf();

Do I need to restart the instance?

What do you get if you execute `select tablespace from pg_tables where tablename = 'teammembers'`? — Bob Jarvis - Слава Україні, Jul 11 '22 at 12:14

score 1 · Accepted Answer · answered Jul 11 '22 at 12:14

EXPLAIN (SETTINGS) only shows database parameters (whatever you can see in pg_settings or with SHOW), but it does not show storage parameters of tables or tablespace options.

I concur that that would be nice information, but it is questionable how that information should be displayed: A query can affect several tables, and each could be in a different tablespace. So you would have to come up with an output format that can deal with that.

I tend to agree that an exception might be made with the default tablespace of the database you are connected to: it is highly likely that most of the tables will use the settings for that tablespace. If you want to suggest such a change in EXPLAIN output, you should contact the pgsql-hackers mailing list. You can promote your cause by writing a patch.

EXPLAIN (ANALYZE, SETTINGS) display wrong tablespace parameters

1 Answers1