Re: performance regression with 9.2

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