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.