On Wed, 20 Aug 2003, Rod Taylor wrote: > On Wed, 2003-08-20 at 17:41, Stephan Szabo wrote: > > On Wed, 20 Aug 2003, Rod Taylor wrote: > > > > > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > > > be changed at the same time, because it really is unusable for anything > > > > over a couple of thousand values. > > > > > > Changed to do what? > > > > One possibility might be to act as if the valuelist was a table and do the > > IN as if it were that way, rather than treating it as a set of ORs. That > > would be basically like doing the temporary table solution, but without > > requiring the user to do it. > > 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: create temp table/copy 10001 entries/select in subquery - .8 sec select in (value list 9998 entries) - ~ 2min 19 sec explain select in (value list) - ~ 4.8 sec ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
