Re: Planner create a slow plan without an available index

Tom Lane wrote:
> Ben-Nes Yonatan <da(at)canaan(dot)co(dot)il> writes:
>
>>Indexes:
>> "items_items_id_key" UNIQUE, btree (items_id)
>> "items_left" btree (left)
>> "items_left_right" btree (left, right)
>
>
> You could get rid of the items_left index --- it's redundant with the
> first column of the combined index anyway.
>
>
>>bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left>=(SELECT left
>>FROM category WHERE category_id=821) AND right<=(SELECT right FROM
>>category WHERE category_id=821) OFFSET 24 LIMIT 13;
>
>
> Doing OFFSET/LIMIT without an ORDER BY is just asking for trouble.
> If you were to specify "ORDER BY left, right" that would probably
> convince the planner to use the index you want.
>
> However ... this query is basically going to suck with any btree index,
> because btree can't usefully do range checks on two separate variables.
> There's an exactly similar problem being discussed over in pgsql-novice:
> http://archives.postgresql.org/pgsql-novice/2005-08/msg00243.php
>
> regards, tom lane

First of all thanks I did succed to use the index that way and to
receive less then 80ms responds, but if imporvement is possible I would
like to do it.

If btree index is not suitable for this query then which index is? as
far as I understand the rtree index doesnt support range checks and the
hash index is not recommended by almost everyone (including the manual)
so the only one left is the gist, is that the most suitable index for
this query? if so can you give me a link as to where I can learn how to
use such an index efficently? (by the way the only link that worked at
the postgresql manual "Chapter 48. GiST Indexes" is the one which direct
to "the University of California at Berkeley's GiST Indexing Project web
site" the other 2 links direct to 404 pages and I guess that they should
be removed).

Thanks alot,
Ben-Nes Yonatan