Re: Unexpected sequential scan on an indexed column

Thanks, Dave.
Eddy

On Mon, Nov 16, 2009 at 1:52 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:

> With Postgres, you can transparently replace a regular select with a
> function that takes the same types and returns a record iterator with the
> same columns. The only change needed is the SQL used to invoke it, you won't
> need any logic changes in your app code (Java or whatever), e.g.
>
> *select ............ where x=:x ......(select ...... where ..... y=:y)
> *
> Becomes
>
> *select myfunction(:x, :y)
> *
>
> On Mon, Nov 16, 2009 at 2:45 PM, Eddy Escardo-Raffo <eescardo(at)kikini(dot)com>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
>>
>> 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
>>>
>>
>>
>