Date: Thu, 21 Aug 2003 10:28:53 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Dani Oderbolz <oderbolz@ecologic.de>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: "SELECT IN" Still Broken in 7.4b
In-Reply-To: <3F44995A.8040308@ecologic.de>
Message-ID: <20030821102627.C51705-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 21 Aug 2003, Dani Oderbolz wrote:

> Stephan Szabo wrote:
>
> >On Wed, 20 Aug 2003, Rod Taylor wrote:
> >...
> >
> >
> >>Is the temp table version any faster?  I realize it has a higher limit
> >>to the number of items you can have in the list.
> >>
> >>
> >
> >Within the scope of the new hashed IN stuff I believe so in at least some
> >cases.  I have a few million row table of integers where searching for
> >values IN (~10000 values) takes longer than creating the temp table,
> >copying into it and doing the in subquery.  That's not a particularly
> >meaningful test case, but sending the psql output to /dev/null gives me: ...
> >
> But where do your values come from in the first place?
> Couldn't you optimize your model so that you don't have to copy around
> such amounts of data?

I wasn't the OP, I was doing a simple test as a comparison between
the two forms of the queries to see if making a temp table and populating
it and then doing the subselect form could ever be faster than the current
conversion for valuelists to a sequence of or conditions.  In 7.3, it
probably was not possible for a conversion to in subselect to be faster,
but with the new hash subquery stuff it was worth trying again.