Skip site navigation (1) Skip section navigation (2)

Re: Planner create a slow plan without an available index

From: Ben-Nes Yonatan <da(at)canaan(dot)co(dot)il>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Planner create a slow plan without an available index
Date: 2005-08-30 09:25:26
Message-ID: 43142606.7050204@canaan.co.il (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2005-08-30 09:32:08
Subject: Re: Planner create a slow plan without an available index
Previous:From: BernardDate: 2005-08-30 09:25:07
Subject: Re: psql from Linux script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group