This is incredibly helpful, Kenneth. I didn't know about the SETOF syntax at all. This could help minimize the amount of refactoring I need to do.
Thanks! Eddy On Mon, Nov 16, 2009 at 12:55 PM, Kenneth Marshall <k...@rice.edu> wrote: > On Mon, Nov 16, 2009 at 12:45:46PM -0800, Eddy Escardo-Raffo wrote: > > Yeah this kind of thing would probably work. Doing this in java with > > separate queries would be easy to code but require multiple round trips. > > Doing it as a stored procedure would be nicer but I'd have to think a > little > > more about how to refactor the java code around the query to make this > > happen. Thanks for the suggestion. > > > > Eddy > > > > Hi Eddy, > > Here is a lookup wrapper that is used in DSPAM to work around > a similar problem. Maybe you can use it as a template for your > function: > > create function lookup_tokens(integer,bigint[]) > returns setof dspam_token_data > language plpgsql stable > as ' > declare > v_rec record; > begin > for v_rec in select * from dspam_token_data > where uid=$1 > and token in (select $2[i] > from generate_series(array_lower($2,1),array_upper($2,1)) s(i)) > loop > return next v_rec; > end loop; > return; > end;'; > > Regards, > Ken > > > On Mon, Nov 16, 2009 at 9:44 AM, Dave Crooke <dcro...@gmail.com> wrote: > > > > > Hi Eddy > > > > > > Perhaps a slightly naive suggestion .... have you considered > > > converting the query to a small stored procedure ('function' in > > > Postgres speak)? You can pull the location values, and then iterate > > > over a query like this: > > > > > > select userid from users where location=:x > > > > > > which is more-or-less guaranteed to use the index. > > > > > > > > > I had a somewhat similar situation recently, where I was passing in a > > > list of id's (from outwith Postgres) and it would on occasion avoid > > > the index in favour of a full table scan .... I changed this to > > > iterate over the id's with separate queries (in Java, but using a > > > function will achieve the same thing) and went from one 5 minute query > > > doing full table scan to a handful of queries doing sub-millisecond > > > direct index lookups. > > > > > > Cheers > > > Dave > > > >