Re: performance regression with 9.2 - Mailing list pgsql-performance

From Willem Leenen
Subject Re: performance regression with 9.2
Date
Msg-id DUB104-W27CF42182896D194898D798F6D0@phx.gbl
Whole thread Raw
In response to Re: performance regression with 9.2  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: performance regression with 9.2  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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@sss.pgh.pa.us
> To: pg@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 <pg@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

pgsql-performance by date:

Previous
From: Dave Cramer
Date:
Subject: Re: performance regression with 9.2
Next
From: Tom Lane
Date:
Subject: Re: performance regression with 9.2