1 Plantuner - enable planner hints
3 contrib/plantuner is a contribution module for PostgreSQL 8.4+, which
6 All work was done by Teodor Sigaev (teodor@sigaev.ru) and Oleg Bartunov
9 Sponsor: Nomao project (http://www.nomao.com)
13 Whether somebody think it's bad or not, but sometime it's very
14 interesting to be able to control planner (provide hints, which tells
15 optimizer to ignore its algorithm in part), which is currently
16 impossible in POstgreSQL. Oracle, for example, has over 120 hints, SQL
17 Server also provides hints.
19 This first version of plantuner provides a possibility to hide
20 specified indexes from PostgreSQL planner, so it will not use them.
22 There are many situation, when developer want to temporarily disable
23 specific index(es), without dropping them, or to instruct planner to
26 Next, for some workload PostgreSQL could be too pessimistic for
27 newly created tables and assumes much more rows in table than
28 it actually has. If plantuner.fix_empty_table GUC variable is set
29 to true then module will set to zero number of pages/tuples of
30 table which hasn't blocks in file.
34 * Get latest source of plantuner from CVS Repository
35 * gmake && gmake install && gmake installcheck
38 plantuner.forbid_index (deprecated)
39 plantuner.disable_index
40 List of indexes invisible to planner
41 plantuner.enable_index
42 List of indexes visible to planner even they are hided
43 by plantuner.disable_index.
45 List of explicitly enabled indexes (overload plantuner.disable_index
46 and plantuner.enable_index), so, only indexes in this list are allowed.
50 To enable the module you can either load shared library 'plantuner' in
51 psql session or specify 'shared_preload_libraries' option in
54 =# create table test(id int);
55 =# create index id_idx on test(id);
56 =# create index id_idx2 on test(id);
59 Column | Type | Modifiers
60 --------+---------+-----------
65 =# explain select id from test where id=1;
67 -----------------------------------------------------------------------
68 Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4)
69 Recheck Cond: (id = 1)
70 -> Bitmap Index Scan on id_idx2 (cost=0.00..4.34 rows=12 width=0)
73 =# set enable_seqscan=off;
74 =# set plantuner.disable_index='id_idx2';
75 =# explain select id from test where id=1;
77 ----------------------------------------------------------------------
78 Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4)
79 Recheck Cond: (id = 1)
80 -> Bitmap Index Scan on id_idx (cost=0.00..4.34 rows=12 width=0)
83 =# set plantuner.disable_index='id_idx2,id_idx';
84 =# explain select id from test where id=1;
86 -------------------------------------------------------------------------
87 Seq Scan on test (cost=10000000000.00..10000000040.00 rows=12 width=4)
90 =# set plantuner.enable_index='id_idx';
91 =# explain select id from test where id=1;
93 -----------------------------------------------------------------------
94 Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4)
95 Recheck Cond: (id = 1)
96 -> Bitmap Index Scan on id_idx (cost=0.00..4.34 rows=12 width=0)