Ed Loehr <pggeneral@bluepolka.net> writes:
> I have a 7.2.1 query with two peculiar characteristics and wondered if
> anyone could offer some insight.
> First, my query takes 90 seconds with a hash join, but only 1 second with
> nested loops.
Probably because the EXISTS subplans only need to fetch one row from
each table they access; that's more or less an ideal case for nestloop
indexscans. Nestloops do not scale very well to large retrieval sets,
however...
> Second, the same query sometimes takes 10-50 seconds shortly after
> possibly a dump or other high-data-volume queries are executed, after
> which it then returns to 1 second execution time. Getting crowded out
> of shared memory?
Sounds like it. What shared-buffers setting are you using? How much
RAM in the box?
> Finally, I am inclined to turn off hash joins altogether.
That would be a remarkably foolish thing to do. Certainly this query
is not a reason to do so; AFAICS the planner will do this one just fine
without any thumb on the scales.
regards, tom lane