Re: Question with hashed IN

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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?

> 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.

regards, tom lane