Movatterモバイル変換

F.54.1. Motivation

F.54.2. GUC Variables

F.54.3. Example

F.54.4. Authors

In some cases, it may be required to control the planner by providing hints that make the optimizer ignore some parts of its algorithm. There are many situations when a developer may want to temporarily disable specific index(es), without dropping them, or to instruct the planner to use a specific index.

This version ofplantuner provides a possibility to hide the specified indexes from Postgres Pro planner, so it will not use them. For some workloads, Postgres Pro could be too pessimistic about newly created tables and assume that there are much more rows in a table than it actually has. If theplantuner.fix_empty_table GUC variable is set totrue,plantuner sets to zero the number of pages/tuples of the table that has no blocks in a file.

plantuner.disable_index — list of indexes invisible to planner.

plantuner.enable_index — list of indexes visible to planner even if they are hidden byplantuner.disable_index.

To enable the module, you can either loadplantuner shared library in apsql session or specifyshared_preload_libraries option inpostgresql.conf.

=# LOAD 'plantuner';=# create table test(id int);=# create index id_idx on test(id);=# create index id_idx2 on test(id);=# \d test     Table "public.test" Column |  Type   | Modifiers--------+---------+----------- id     | integer |Indexes:    "id_idx" btree (id)    "id_idx2" btree (id)=# explain select id from test where id=1;                              QUERY PLAN----------------------------------------------------------------------- Bitmap Heap Scan on test  (cost=4.34..15.03 rows=12 width=4)   Recheck Cond: (id = 1)   ->  Bitmap Index Scan on id_idx2  (cost=0.00..4.34 rows=12 width=0)         Index Cond: (id = 1)(4 rows)=# set enable_seqscan=off;=# set plantuner.disable_index='id_idx2';=# explain select id from test where id=1;                              QUERY PLAN---------------------------------------------------------------------- Bitmap Heap Scan on test  (cost=4.34..15.03 rows=12 width=4)   Recheck Cond: (id = 1)   ->  Bitmap Index Scan on id_idx  (cost=0.00..4.34 rows=12 width=0)         Index Cond: (id = 1)(4 rows)=# set plantuner.disable_index='id_idx2,id_idx';=# explain select id from test where id=1;                               QUERY PLAN------------------------------------------------------------------------- Seq Scan on test  (cost=10000000000.00..10000000040.00 rows=12 width=4)   Filter: (id = 1)(2 rows)=# set plantuner.enable_index='id_idx';=# explain select id from test where id=1;                              QUERY PLAN----------------------------------------------------------------------- Bitmap Heap Scan on test  (cost=4.34..15.03 rows=12 width=4)   Recheck Cond: (id = 1)   ->  Bitmap Index Scan on id_idx  (cost=0.00..4.34 rows=12 width=0)         Index Cond: (id = 1)(4 rows)

All work was done by Teodor Sigaev (teodor@sigaev.ru) and Oleg Bartunov (oleg@sai.msu.su).

The work sponsored by Nomao project (http://www.nomao.com).