Re: Question with hashed IN

On Sun, 17 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Basically, the first thing I noticed was that changing reltuples
> > on the pg_class row for a table affected the speed of
> > explain analyze select * from othertable where foo not in (select bar from
> > table);
> > even when the plan wasn't changing, seqscan + filter on hashed subquery.
>
> That doesn't make any sense to me --- AFAICS, only the planner pays any
> attention to reltuples, so it could only affect things via changing the
> plan.  Could we see details?

I've included a perl file that generates data like that I was using and
the output of the commands from that through psql -E on my machine.  The
times seem pretty repeatable in any order so caching and such doesn't seem
to be playing a big part.

> > Then I noted that changing sort_mem changed the point at which it would
> > choose a hashed subquery in the initial plan based on the estimated
> > tuples, but didn't seem to actually affect the real memory usage,
>
> Yeah, the hashed=subquery code doesn't make any attempt to spill to
> disk.  So if the planner's estimate is badly off, you could see actual
> usage well in excess of sort_mem.

Ah, that makes sense then.