Re: Planner create a slow plan without an available index

On Tue, Aug 30, 2005 at 11:25:26AM +0200, Ben-Nes Yonatan wrote:
> Tom Lane wrote:
> >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
>
> 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).

Basically, no index is really setup for the query as you wrote it.
Indexes generally improve performance by taking advantage of order. You
have two constants (the two subqueries) and two variables (left and
right). Andyou applying range range (not equality) to each one. There
is no way to order an index that would give the answer you want.

rtree works on multidimesional (geometric) data. It can do range tests
(is object A to the left of object B) but it's only applicable if your
conditions can be interpreted that way.

GiST is for creating custom index types, hardly likely to be useful
in your case.

I can't tell from your query (and PostgreSQL certainly can't) but are
there other constraints such left <= right or something similar for the
constants. If so, maybe adding that will help PostgreSQL optimise your
query. Maybe indexing on (left+right) might work for you but it all
depends on the structure of your data.

If you want more help, you're going to need to provide information on
your database including what left, right and items actually are.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.