Ben-Nes Yonatan <da@xxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
