On 1/10/11 12:37 PM, "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> wrote: >Scott Carey <scott@xxxxxxxxxxxxxxxxx> wrote: > >> Often, the best query plans result from 'LEFT JOIN WHERE right >> side is NULL' rather than NOT EXISTS however. I often get >> performance gains by switching NOT EXISTS queries to LEFT JOIN >> form. > >Even in 8.4 and later? I would think that the anti-join that Tom >added in 8.4 would always perform at least as well as the LEFT JOIN >technique you describe. > >-Kevin Yes, in 8.4. The query planner definitely does not treat the two as equivalent. I don't have a concrete example at hand right now, but I've been working exclusively on 8.4 since a month after it was released. It does often use an anti-join for NOT EXISTS, but does not seem to explore all avenues there. Or perhaps the statistics it has differ for some reason at that point. All I know, is that the resulting query plan differs sometimes and I'd say 3 out of 4 times the LEFT JOIN variant is more optimal when they differ. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
