On Mon, Nov 16, 2009 at 1:52 PM, Dave Crooke <dcrooke@gmail.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@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 <dcrooke@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.
