|
Hello Tom,
Could you elaborate on this? I'm trying to learn the explain plans of postgresql and i would like to know if we're looking at the same clue's. To me, i see a mismatch between the optimizer and the actual records retrieved in the fast SQL as well, so plan instability is a realistic scenario. For the slow query, I thought to see a problem in the part below the ' recursive union' : the HASH join is more expensive that the nested loop. ( hints are not yet implemented in Postgresql , aren't they? ) So the SQL text is: explain analyze SELECT note_sets."id" AS t0_r0, ... notes."updated_by" AS t2_r10 FROM note_sets LEFT OUTER JOIN note_set_sources ON note_set_sources.id = note_sets.note_set_source_id LEFT OUTER JOIN notes ON notes.note_set_id = note_sets.id AND notes."status" = E'A' WHERE (note_sets.id IN (WITH RECURSIVE parent_noteset as (SELECT id FROM note_sets where id = 8304085 UNION SELECT note_sets.id FROM parent_noteset parent_noteset, note_sets note_sets WHERE note_sets.parent_id = parent_noteset.id) SELECT id FROM parent_noteset)) IMHO, the plan goes wrong at the part SELECT note_sets.id FROM parent_noteset parent_noteset, note_sets note_sets WHERE note_sets.parent_id = parent_noteset.id) Do you agree? > From: tgl@xxxxxxxxxxxxx > To: pg@xxxxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: performance regression with 9.2 > Date: Mon, 12 Nov 2012 15:43:53 -0500 > > Dave Cramer <pg@xxxxxxxxxxxxx> writes: > > This query is a couple orders of magnitude slower the first result is > > 9.2.1, the second 9.1 > > Hm, the planner's evidently doing the wrong thing inside the recursive > union, but not obvious why. Can you extract a self-contained test case? > > regards, tom lane > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance |
