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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
