Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
2010/4/29 Jaime Casanova jcasa...@systemguards.com.ec: 2009/10/12 Teodor Sigaev teo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
2009/10/12 Teodor Sigaev teo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
Kevin Grittner wrote: Robert Haas robertmh...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Tue, Oct 13, 2009 at 10:14 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
2009/10/12 Teodor Sigaev teo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Mon, Oct 12, 2009 at 11:31:24AM -0400, Robert Haas wrote: 2009/10/12 Teodor Sigaev teo...@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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
