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: t...@sss.pgh.pa.us
> To: p...@fastcrypt.com
> CC: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] performance regression with 9.2
> Date: Mon, 12 Nov 2012 15:43:53 -0500
>
> Dave Cramer <p...@fastcrypt.com> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
