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?
CodePudding user response:
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.