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/