Re: Hash Join vs Nested Loops in 7.2.1 ...

Ed Loehr <pggeneral(at)bluepolka(dot)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