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

Oleg Bartunov wrote:
> =# set enable_seqscan=off;
> =# set plantuner.forbid_index='id_idx2';

The genius of this module is the line above -- a more fine-grained way
to control the optimizer, with specific index disabling.

> =# 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)

Are you planning to submit this as a /contrib module?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Fri, 9 Oct 2009, Bruce Momjian wrote:

> Oleg Bartunov wrote:
>> =# set enable_seqscan=off;
>> =# set plantuner.forbid_index='id_idx2';
>
> The genius of this module is the line above -- a more fine-grained way
> to control the optimizer, with specific index disabling.
>
.................
> Are you planning to submit this as a /contrib module?

No plan yet.

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

> Are you planning to submit this as a /contrib module?

I haven't objections to do that, we don't planned that because we know sceptical
relation of community to hints :)
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:
>
>
> > Are you planning to submit this as a /contrib module?
>
> I haven't objections to do that, we don't planned that because we know sceptical
> relation of community to hints :)

Well, the nice thing about this patch is that the hints are mostly
external to the backend, and are not installed by default. I think it
would make a great /contrib module.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

2009/10/12 Teodor Sigaev <teodor(at)sigaev(dot)ru>:
>> Are you planning to submit this as a /contrib module?
>
> I haven't objections to do that, we don't planned that because we know
> sceptical
> relation of community to hints :)

I think it would be pretty useful to have some additional knobs to
poke at the planner. I sometimes want to know what the planner thinks
the cost of some plan other than the one actually selected would be.
For simple queries, it's often possible to accomplish this by using
the enable_* parameters, but those are a pretty coarse instrument and
what you can do with them is fairly limited. So I think it would be
nice to have some more options, and I wouldn't object to including
this as one of them, provided that the code isn't too much of a
kludge.

That having been said, my tables don't tend to be heavily indexed and
the planner basically never picks the wrong one. Most of my query
planning problems (and many of the ones on -performance) are the
result of bad selectivity estimates. So what I'd really like to see
is a way to override the selectivity of a given expression. Making
the planner smarter about estimating selectivity in the first place
would be *great*, too, but I don't have much hope that it's ever going
to be perfect.

...Robert

On Mon, Oct 12, 2009 at 11:31:24AM -0400, Robert Haas wrote:
> 2009/10/12 Teodor Sigaev <teodor(at)sigaev(dot)ru>:
> >> Are you planning to submit this as a /contrib module?
> >
> > I haven't objections to do that, we don't planned that because we
> > know sceptical relation of community to hints :)
>
> I think it would be pretty useful to have some additional knobs to
> poke at the planner.

A contrib module would certainly help test that idea, at least as far
as any knobs it provides.

> I sometimes want to know what the planner thinks the cost of some
> plan other than the one actually selected would be. For simple
> queries, it's often possible to accomplish this by using the
> enable_* parameters, but those are a pretty coarse instrument and
> what you can do with them is fairly limited. So I think it would be
> nice to have some more options, and I wouldn't object to including
> this as one of them, provided that the code isn't too much of a
> kludge.
>
> That having been said, my tables don't tend to be heavily indexed
> and the planner basically never picks the wrong one. Most of my
> query planning problems (and many of the ones on -performance) are
> the result of bad selectivity estimates. So what I'd really like to
> see is a way to override the selectivity of a given expression.
> Making the planner smarter about estimating selectivity in the first
> place would be *great*, too, but I don't have much hope that it's
> ever going to be perfect.

Nathan Boley (cc'd) has proposed smartening it up by figuring out what
class of distributions the table looks like it belongs to and acting
on that. Unsure how far this got as far as code, but I suspect Nathan
can address this :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Oct 12, 2009 at 9:20 AM, Hans-Juergen Schoenig -- PostgreSQL
<postgres(at)cybertec(dot)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.

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I sometimes want to know what the planner thinks the cost of some
> plan other than the one actually selected would be.

Another DBMS I used for years had a way to turn on an *extremely*
verbose mode for their planner; it showed everything it considered
with its related cost information. Even a moderately complex query
generated hundreds or thousands of lines of output, so I rarely used
it; but for those particularly stubborn queries, where you just can't
understand why it's picking the plan it is, a little work wading
through the output would *always* clear up the mystery.

Now that we can generate EXPLAIN output in more structured formats,
perhaps we could think about adding an "extremely verbose" mode where
the planner would "think out loud" as a whole separate section from
where we show the chosen plan?

-Kevin

Kevin Grittner wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> > I sometimes want to know what the planner thinks the cost of some
> > plan other than the one actually selected would be.
>
> Another DBMS I used for years had a way to turn on an *extremely*
> verbose mode for their planner; it showed everything it considered
> with its related cost information. Even a moderately complex query
> generated hundreds or thousands of lines of output, so I rarely used
> it; but for those particularly stubborn queries, where you just can't
> understand why it's picking the plan it is, a little work wading
> through the output would *always* clear up the mystery.
>
> Now that we can generate EXPLAIN output in more structured formats,
> perhaps we could think about adding an "extremely verbose" mode where
> the planner would "think out loud" as a whole separate section from
> where we show the chosen plan?

Well, we have OPTIMIZER_DEBUG, which is a compile-time flag, but that
perhaps can be changed to output as part of EXPLAIN.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tue, 2009-10-13 at 09:14 -0500, Kevin Grittner wrote:
> Now that we can generate EXPLAIN output in more structured formats,
> perhaps we could think about adding an "extremely verbose" mode where
> the planner would "think out loud" as a whole separate section from
> where we show the chosen plan?

Tom Raney did that a while back:

http://archives.postgresql.org/pgsql-patches/2008-07/msg00011.php

He also had an accompanying visual tool to navigate the output in a
meaningful way.

If he has moved on to other projects, it would be great if someone could
pick it up.

Regards,
Jeff Davis

On Tue, 2009-10-13 at 11:26 -0700, Jeff Davis wrote:
> On Tue, 2009-10-13 at 09:14 -0500, Kevin Grittner wrote:
> > Now that we can generate EXPLAIN output in more structured formats,
> > perhaps we could think about adding an "extremely verbose" mode where
> > the planner would "think out loud" as a whole separate section from
> > where we show the chosen plan?
>
> Tom Raney did that a while back:
>
> http://archives.postgresql.org/pgsql-patches/2008-07/msg00011.php
>
> He also had an accompanying visual tool to navigate the output in a
> meaningful way.
>
> If he has moved on to other projects, it would be great if someone could
> pick it up.

No kidding. It was a very cool project. Here is a video of a
presentation he did at West 2008:

http://www.vimeo.com/4101141

Joshua D. Drake

>
> Regards,
> Jeff Davis
>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Tue, Oct 13, 2009 at 10:14 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> I sometimes want to know what the planner thinks the cost of some
>> plan other than the one actually selected would be.
>
> Another DBMS I used for years had a way to turn on an *extremely*
> verbose mode for their planner; it showed everything it considered
> with its related cost information.  Even a moderately complex query
> generated hundreds or thousands of lines of output, so I rarely used
> it; but for those particularly stubborn queries, where you just can't
> understand why it's picking the plan it is, a little work wading
> through the output would *always* clear up the mystery.
>
> Now that we can generate EXPLAIN output in more structured formats,
> perhaps we could think about adding an "extremely verbose" mode where
> the planner would "think out loud" as a whole separate section from
> where we show the chosen plan?

I wouldn't object to such a thing, but for simple cases I think it
would be more convenient to modify the planner's assumptions and then
try replanning. An exhaustive dump of everything the planner has
considered is going to be a LOT of data, and I don't really want to
have to set up a graphical visualization tool every time I have a
planning question. I am a command-line kind of guy...

...Robert

On Tue, 13 Oct 2009, Robert Haas wrote:

> An exhaustive dump of everything the planner has considered is going to
> be a LOT of data, and I don't really want to have to set up a graphical
> visualization tool every time I have a planning question. I am a
> command-line kind of guy...

Wouldn't this be easy enough to cope with in a scripting language though?
If the planner produces the comprehensive report via something like XML,
that moves the problem of how to best present that into user space, where
I think it belongs at least at first. I'm sure someone can produce an
example program in Perl or Python that produces a fairly collapsed tree
via command line and then allows expanding on bits you want more detail
on. That's the sort of development you can easily get people to do, as
opposed to the dreary details of exporting the detail in the first place.

Get the full report out there, and I'm sure we can produce terse ones in
user-space; once that's nailed down and explored, maybe then it's
appropriate to talk about how to provide squished versions directly. As
already pointed out, some people are never going to be satisfied with
anything other than the most detail possible, so you might as well start
with that if the simpler views can be derived from them.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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(at)decibel(dot)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(at)thebuild(dot)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(at)decibel(dot)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

2009/10/12 Teodor Sigaev <teodor(at)sigaev(dot)ru>:
>
>
>> Are you planning to submit this as a /contrib module?
>
> I haven't objections to do that, we don't planned that because we know
> sceptical
> relation of community to hints :)

this could be very useful now that we have HS and we aren't able to
use hash indexes on the slave so we can advice to disable those
indexes there

the only problem is that seems like we can't put
"plantuner.forbid_index='a_hash_index'" on postgresql.conf ala
auto_explain, that could make this better

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

2010/4/29 Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>:
> 2009/10/12 Teodor Sigaev <teodor(at)sigaev(dot)ru>:
>>
>>
>>> Are you planning to submit this as a /contrib module?
>>
>> I haven't objections to do that, we don't planned that because we know
>> sceptical
>> relation of community to hints :)
>
> this could be very useful now that we have HS and we aren't able to
> use hash indexes on the slave so we can  advice to disable those
> indexes there

if we know that, can the planner now that too ?

>
> the only problem is that seems like we can't put
> "plantuner.forbid_index='a_hash_index'" on postgresql.conf ala
> auto_explain, that could make this better
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain