Hi there,
this is an announcement of our new contribution module for PostgreSQL -
Plantuner - enable planner hints
(http://www.sai.msu.su/~megera/wiki/plantuner).
Example:
=# 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.forbid_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.forbid_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)
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83