Re: combined indexes with Gist - planner issues?

On Mon, Aug 31, 2009 at 04:06:22PM +0200, Hans-Juergen Schoenig -- PostgreSQL wrote:
> ok, i thought it would be something gist specific i was not aware of.
> the golden question now is: i am looking for the cheapest products given
> a certain text in an insane amount of data.
> how to do it? other quals which could narrow down the amount of data
> would not help.
>
> i cannot see an option with regular "weapons" ...
> maybe you can an idea how to fix core to make it work? maybe there is a
> mechanism we could need.
> we really have to make this work - no matter what it takes.
> we are willing to put effort into that.

The way I usually attack such a problem is to think of a data
structure+algorithm that could produce the output you want. Once you've
got that it's usually clear how you can make postgres do it and what
changes would need to be made.

At first glance I don't see any nice data structure specific for your
problem. But it occurs to me that maybe you could just have a (btree)
index on the price and just scan in asceding order until you have
enough records. Expensive if the first record is expensive.

Another possibility is to change your query to use the price in the
GiST index: execute multiple queries of the form:

... AND display_price >= 0.01 and display_price < 1;
... AND display_price >= 1 and display_price < 10;

Because you match less records the sort won't be so expensive and you
can stop once you have enough records.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.