Обсуждение: 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
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@momjian.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@sai.msu.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@sigaev.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: contrib/plantuner - enable PostgreSQL planner hints
От
Hans-Juergen Schoenig -- PostgreSQL
Дата:
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
2009/10/12 Teodor Sigaev <teodor@sigaev.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@sigaev.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Robert Haas <robertmhaas@gmail.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@gmail.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@momjian.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.Grittner@wicourts.gov> wrote: > Robert Haas <robertmhaas@gmail.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@gregsmith.com http://www.gregsmith.com Baltimore, MD
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 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
2009/10/12 Teodor Sigaev <teodor@sigaev.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@systemguards.com.ec>: > 2009/10/12 Teodor Sigaev <teodor@sigaev.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain