Re: Planner create a slow plan without an available index

Ben-Nes Yonatan <da@canaan.co.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