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
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
>
