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.
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 EOF ANALYZE; VACUUM FULL FREEZE; EOF Could this be done better in the one step VACUUM FULL FREEZE ANALYZE or ANALYZING after the VACUUM FULL? 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: 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 EOF 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
[HACKERS] Optimizer generates bad plans.
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. Kris Jurka The query tries to determine what foreign keys exists between the following tables. create table people (id int4 primary key, name text); create table policy (id int4 primary key, name text); create table users (id int4 primary key, people_id int4, policy_id int4, CONSTRAINT people FOREIGN KEY (people_id) references people(id), constraint policy FOREIGN KEY (policy_id) references policy(id)); SELECT DISTINCT n.nspname as pnspname, n2.nspname as fnspname, c.relname as prelname, c2.relname as frelname, t.tgconstrname, a.attnum as keyseq, ic.relname as fkeyname, t.tgdeferrable, t.tginitdeferred, t.tgnargs,t.tgargs, p1.proname as updaterule, p2.proname as deleterule FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE (t.tgrelid=c.oid AND t.tgisconstraint AND t.tgconstrrelid=c2.oid AND t.tgfoid=p1.oid and p1.proname like 'RI\_FKey\_%\_upd') AND (t1.tgrelid=c.oid and t1.tgisconstraint and t1.tgconstrrelid=c2.oid AND t1.tgfoid=p2.oid and p2.proname like 'RI\_FKey\_%\_del') AND i.indrelid=c.oid AND i.indexrelid=ic.oid AND ic.oid=a.attrelid AND i.indisprimary AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid AND c2.relname='users' ORDER BY prelname,keyseq ; Unique (cost=1.06..1.10 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) - Sort (cost=1.06..1.07 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) Sort Key: c.relname, a.attnum, n.nspname, n2.nspname, c2.relname, t.tgconstrname, ic.relname, t.tgdeferrable, t.tginitdeferred, t.tgnargs, t.tgargs, p1.proname, p2.proname - Merge Join (cost=1.03..1.05 rows=1 width=594) (actual time=844522. 00..845786.00 rows=2 loops=1) Merge Cond: (outer.tgconstrrelid = inner.tgconstrrelid) Join Filter: ((inner.tgfoid = outer.oid) AND (inner.tgrelid = outer.oid)) - Nested Loop (cost=0.00..27709.41 rows=1 width=510) (actual time=844522.00..845786.00 rows=12 loops=1) Join Filter: ((inner.indexrelid = outer.oid) AND (inner.indrelid = outer.oid)) - Nested Loop (cost=0.00..27706.67 rows=1 width=502) (actual time=843375.00..843954.00 rows=10620 loops=1) Join Filter: ((inner.tgconstrrelid = outer.oid) AND (outer.relnamespace = inner.oid)) - Index Scan using pg_class_oid_index on pg_class c2 (cost=0.00..15.67 rows=1 width=72) (actual time=1.00..1.00 rows=1 loops=1) Filter: (relname = 'users'::name) - Materialize (cost=27690.93..27690.93 rows=4 width=430) (actual time=843374.00..843781.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27690.93 rows=4 width=430) (actual time=614674.00..843125.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27689.85 rows=1 width=362) (actual time=614674.00..842368.00 rows=10620 loops=1) Join Filter: ((outer.tgfoid = inner.oid) AND (outer.tgrelid = inner.oid)) - Seq Scan on pg_trigger t1 (cost=0.00..1.02 rows=1 width=12) (actual time=0.00..1.00 rows=6 loops=1) Filter: tgisconstraint - Materialize (cost=26180.37..26180.37 rows=100564 width=350) (actual time=83492.50..135359.33 rows=3637350 loops=6) - Nested Loop (cost=0.00..26180.37 rows=100564 width=350) (actual time=68978.00..481414.00 rows=3637350 loops=1) Join Filter: (inner.relnamespace = outer.oid) - Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=68) (actual time=0.00..0.00 rows=4 loops=1) - Materialize (cost=5287.78..5287.78 rows=100564 width=282) (actual time=17273.75..110243.25 rows=3637350 loops=4)
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.
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.
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.
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.
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])
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.
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
