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 <eesca...@kikini.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 <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
>>
>
>
