Re: Question with hashed IN - Mailing list pgsql-hackers

From Stephan Szabo
Subject Re: Question with hashed IN
Date
Msg-id 20030816223402.A78105-201000@megazone.bigpanda.com
Whole thread Raw
In response to Re: Question with hashed IN  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Question with hashed IN
List pgsql-hackers
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.

pgsql-hackers by date:

Previous
From: Joe Conway
Date:
Subject: Re: char() datatype looses strings of all spaces
Next
From: Tom Lane
Date:
Subject: Re: char() datatype looses strings of all spaces