Re: Unexpected sequential scan on an indexed column

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 <dcrooke(at)gmail(dot)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
>