Re: Question with hashed IN

On Sun, 17 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Sun, 17 Aug 2003, Tom Lane wrote:
> >> 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.
>
> Oh, I see what it is. The initial sizing of the hash table (number of
> buckets) is done using the planner's estimate of the number of rows out
> of the subplan. In your later examples, the hash table is woefully
> overloaded and so searching it takes longer (too many items on each
> hash chain).
>
> I'm not sure how important this is to work on. We could try to make the
> executor's hash code more able to adapt when the hash table grows beyond
> what it was expecting (by rehashing, etc) but personally I'd rather spend
> the time on trying to improve the estimate to begin with.

Right.

In case you're wondering, this all came out of playing with doing the NOT
IN query for the ALTER TABLE ADD CONSTRAINT stuff for foreign keys where
those values are potentially still default when the alter occurs. I've
built a not quite complete version using NOT IN and another with NOT
EXISTS (neither handles the 0 column case, although I don't think you can
actually specify it syntactically, and doesn't handle permissions
correctly if the owner can't read the other table) for performance testing
purposes. The things I noticed were side effects of watching that
distilled into simpler tests.