Обсуждение: contrib/plantuner - enable PostgreSQL planner hints

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

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@sai.msu.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

On Mon, Oct 12, 2009 at 9:20 AM, Hans-Juergen Schoenig -- PostgreSQL
<postgres@cybertec.at> wrote:

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

Well, contrib is a good place to start.  It sets a clearly defined
ownership / maintenance person, and if the core of pgsql changes,
instead of the core hackers having to make this code happy, that job
then falls to the original contributor or whoever is willing to keep
it fed and working.

I think I'm gonna grab a copy and play with it, it sure could help in
the cases where the query planner just can't make the right decision
(correlation of indexes etc)  Thanks to Oleg for this a bunch.

On Oct 6, 2009, at 2:57 PM, Oleg Bartunov wrote:
> this is an announcement of our new contribution module for
> PostgreSQL - Plantuner - enable planner hints
> (http://www.sai.msu.su/~megera/wiki/plantuner).
>
> =# set enable_seqscan=off;
> =# set plantuner.forbid_index='id_idx2';


Out of curiosity, did you look at doing hints as comments in a query?
I'm guessing you couldn't actually do that in just a contrib module,
but it's how Oracle handles hints, and it seems to be *much* more
convenient, because a hint only applies for a specific query. I think
it's extremely unlikely you would intentionally want the same hint to
apply to a bunch of queries, and extremely likely that you could
accidentally forget to re-enable something.

That said, thanks for contributing this!
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On Oct 16, 2009, at 10:04 AM, decibel wrote:
> Out of curiosity, did you look at doing hints as comments in a query?

I don't think that a contrib module could change the grammar.

--
-- Christophe Pettus
    xof@thebuild.com

On Fri, 2009-10-16 at 12:04 -0500, decibel wrote:
> I'm guessing you couldn't actually do that in just a contrib module,
> but it's how Oracle handles hints, and it seems to be *much* more
> convenient, because a hint only applies for a specific query.

If that's the only reason, that seems easy enough to solve by using SET
right before the query. SET LOCAL might be convenient if you want to
forget the setting after the query. Connection pool software will do a
RESET ALL anyway.

There are reasons that it might be convenient to use hints inside the
query itself -- for instance, if you want something to apply only to a
subquery. I'm still hoping that someone will come up with a more elegant
solution to solve that problem though.

Regards,
    Jeff Davis

On Fri, Oct 16, 2009 at 10:04 AM, decibel <decibel@decibel.org> wrote:
> Out of curiosity, did you look at doing hints as comments in a query? I'm
> guessing you couldn't actually do that in just a contrib module, but it's
> how Oracle handles hints, and it seems to be *much* more convenient, because
> a hint only applies for a specific query

Fwiw Oracle is moving away from this now. At OpenWorld the optimizer
folks were pleading with folks to get rid of all their hard-coded
hints by preparing plans for hinted queries and loading those as the
approved plans. In 11g there's a system which ensures the database
will not run any plan that isn't approved.

In fact it looks an *awful* lot like the system I sketched out 6 years
ago in: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/11385

--
greg