Re: Unexpected sequential scan on an indexed column

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 <ktm(at)rice(dot)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 <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
> > >
>