Re: Performance of full outer join in 8.3

On Thu, Apr 16, 2009 at 01:31:45PM +0200, Christian Schröder wrote:
> Stupid question: Do I have to analyze again or perform a reindex after
> adding the index?

No, it's a regression in PG's handling of outer joins---it used to
realise that this was a possible optimisation, but now it doesn't.

Tom Lane started discussion on -hackers about this issue:

  http://archives.postgresql.org/pgsql-hackers/2009-04/msg00849.php

it looks as though performance in 8.3 is going to be bad until this
behaviour is changed.  A possible fix is to rewrite your query to work
around the problem:

  SELECT isin
  FROM (SELECT * FROM ts_frontend.attachment_isins WHERE attachment = 2698120) a
    FULL OUTER JOIN (SELECT * FROM ts_frontend.rec_isins WHERE attachment = 2698120) USING (isin)
  GROUP BY isin
  LIMIT 1000;

It looks as though what you're trying to do could also be expressed as:

  SELECT isin FROM ts_frontend.rec_isins WHERE attachment = 2698120
  UNION
  SELECT isin FROM ts_frontend.attachment_isins WHERE attachment = 2698120;

not sure if it's part of something larger so this may not be a useful
transform.

--
  Sam  http://samason.me.uk/