Re:

* Tyrrill, Ed (tyrrill_ed(at)emc(dot)com) wrote:
> Based on all this we will be going with 8.2.4.1, but it seems like
> currently the query planner isn't choosing the best plan for this case.

Was the 'work_mem' set to the same thing on all these runs? Also, you
might try increasing the 'work_mem' under 8.2.4, at least for this query
(you can set it by just doing: set work_mem = '2GB'; or similar in psql,
or you can change the default in postgresql.conf).

The big thing of note, it seems, is that you've got enough memory and
it's coming out faster when doing a hash-join vs. a sort + merge-join.
Could likely be because it doesn't think there's enough work memory
available for the hash, which might change based on the values it gets
from the statistics on how frequently something shows up, etc.

Enjoy,

Stephen