Re: Unexpected sequential scan on an indexed column

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