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/
