Re: [HACKERS] contrib/plantuner - enable PostgreSQL planner hints

hi there ...

for this work i will include you in my evening prayers for at least one
week.
i know there has been a lot of discussion about this but what you just
posted it excellent and more important: USEFUL to many people.

i had something else in mind recently as well: virtual indexes. it would
help people to decide whether and index would make sense if it would
actually exist. in some cases this would make sense as well as many
datasets are just to big to try out if an index help.s

if there was a vote whether this should be in contrib or in core: +999
from me ...

many thanks,

hans

Oleg Bartunov wrote:
> 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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>

--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de