Re: [HACKERS] Optimizer generates bad plans.
Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > Interesting. The inconsistency you're seeing is a result of GEQO. I > > would have hoped that it would have produced a better quality plan > > more often, but apparently not. On my system, the regular query > > optimizer handily beats GEQO for this query: it produces more > > efficienty query plans 100% of the time and takes less time to do so. > > For *this* query at least, raising geqo_threshold would be a good > > idea, but that may not be true universally. > > The current GEQO threshold was set some time ago; since then, the > regular optimizer has been improved while the GEQO code hasn't been > touched. It might well be time to ratchet up the threshold. > > Anyone care to do some additional experiments? Added to TODO: * Check GUC geqo_threshold to see if it is still accurate -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Optimizer generates bad plans.
Kris Jurka <[EMAIL PROTECTED]> writes: > Looking at the differences in statistics before and after the ANALYZE the > only differences are in correlation. This comes from initdb around line > 1046... > "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null < ANALYZE; > VACUUM FULL FREEZE; > EOF > Could this be done better in the one step VACUUM FULL FREEZE ANALYZE or > ANALYZING after the VACUUM FULL? Hm. We can't do it like that, because that would leave the pg_statistic rows unfrozen. I suppose we could do VACUUM FULL; ANALYZE; VACUUM FREEZE; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Kris Jurka wrote: > > On Thu, 19 Sep 2002, Tom Lane wrote: > > > Kris Jurka <[EMAIL PROTECTED]> writes: > > > While adding schema support to the JDBC Driver, I came across a query > > > which occasionally generates some spectacularly bad plans. > > > > Hm, does an ANALYZE help? > > > > Yes, it does, but I don't understand why. The query is entirely against > pg_catalog tables which have had all of three tables added to them. How > can the new ANALYZE stats be significantly different than what came from > the ANALYZED template1. > > Kris Jurka > Looking at the differences in statistics before and after the ANALYZE the only differences are in correlation. This comes from initdb around line 1046... "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null
Re: [HACKERS] Optimizer generates bad plans.
Neil Conway <[EMAIL PROTECTED]> writes: > Interesting. The inconsistency you're seeing is a result of GEQO. I > would have hoped that it would have produced a better quality plan > more often, but apparently not. On my system, the regular query > optimizer handily beats GEQO for this query: it produces more > efficienty query plans 100% of the time and takes less time to do so. > For *this* query at least, raising geqo_threshold would be a good > idea, but that may not be true universally. The current GEQO threshold was set some time ago; since then, the regular optimizer has been improved while the GEQO code hasn't been touched. It might well be time to ratchet up the threshold. Anyone care to do some additional experiments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Tom Lane wrote: > Kris Jurka <[EMAIL PROTECTED]> writes: > > While adding schema support to the JDBC Driver, I came across a query > > which occasionally generates some spectacularly bad plans. > > Hm, does an ANALYZE help? > Yes, it does, but I don't understand why. The query is entirely against pg_catalog tables which have had all of three tables added to them. How can the new ANALYZE stats be significantly different than what came from the ANALYZED template1. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer generates bad plans.
Kris Jurka <[EMAIL PROTECTED]> writes: > While adding schema support to the JDBC Driver, I came across a > query which occasionally generates some spectacularly bad plans. Interesting. The inconsistency you're seeing is a result of GEQO. I would have hoped that it would have produced a better quality plan more often, but apparently not. On my system, the regular query optimizer handily beats GEQO for this query: it produces more efficienty query plans 100% of the time and takes less time to do so. For *this* query at least, raising geqo_threshold would be a good idea, but that may not be true universally. > I thought someone might be interested in a test case for the > optimizer. Thanks, it's a useful query -- I've been meaning to take a look at GEQO for a while now... Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Optimizer generates bad plans.
Maybe not nice, but there's only 32 (64 now?) of them... JOIN pg_attribute WHERE attnum IN (conkeys[1], conkeys[2], conkeys[3], ..., conkeys[32]) Great fun... On Thu, 2002-09-19 at 18:31, Kris Jurka wrote: > > Well I was really hoping pg_constraint would solve all my problems, but > since contrib/array is not installed by default the conkeys and confkeys > columns aren't terribly useful because they can't be joined to > pg_attribute. > > Also there is not a column to tell you the unique constraint that > supports a given foreign key constraint. > > See my post to bugs: > > http://fts.postgresql.org/db/mw/msg.html?mid=1074855 > > Kris Jurka > > > On Thu, 19 Sep 2002, Bruce Momjian wrote: > > > > > Congratulations. That is the largest plan I have ever seen. ;-) > > > > --- > > > > Kris Jurka wrote: > > > While adding schema support to the JDBC Driver, I came across a query > > > which occasionally generates some spectacularly bad plans. I have > > > attached the query and explain analyze outputs against today's cvs head > > > for queries that take between 9 and 845941 msec. In the JDBC Driver I > > > will specify a reasonable join order using explicit JOINs, but I thought > > > someone might be interested in a test case for the optimizer. > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Rod Taylor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer generates bad plans.
Kris Jurka <[EMAIL PROTECTED]> writes: > While adding schema support to the JDBC Driver, I came across a query > which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Optimizer generates bad plans.
Well I was really hoping pg_constraint would solve all my problems, but since contrib/array is not installed by default the conkeys and confkeys columns aren't terribly useful because they can't be joined to pg_attribute. Also there is not a column to tell you the unique constraint that supports a given foreign key constraint. See my post to bugs: http://fts.postgresql.org/db/mw/msg.html?mid=1074855 Kris Jurka On Thu, 19 Sep 2002, Bruce Momjian wrote: > > Congratulations. That is the largest plan I have ever seen. ;-) > > --- > > Kris Jurka wrote: > > While adding schema support to the JDBC Driver, I came across a query > > which occasionally generates some spectacularly bad plans. I have > > attached the query and explain analyze outputs against today's cvs head > > for queries that take between 9 and 845941 msec. In the JDBC Driver I > > will specify a reasonable join order using explicit JOINs, but I thought > > someone might be interested in a test case for the optimizer. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Optimizer generates bad plans.
Congratulations. That is the largest plan I have ever seen. ;-) --- Kris Jurka wrote: > While adding schema support to the JDBC Driver, I came across a query > which occasionally generates some spectacularly bad plans. I have > attached the query and explain analyze outputs against today's cvs head > for queries that take between 9 and 845941 msec. In the JDBC Driver I > will specify a reasonable join order using explicit JOINs, but I thought > someone might be interested in a test case for the optimizer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
