Index search order hints for R-Tree indexes

From: Guy Thornley <guy(at)esphion(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Index search order hints for R-Tree indexes
Date: 2005-02-14 03:32:55
Message-ID: 20050214033255.GU9104@conker.esphion.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Im really noob when it comes to R-Tree indexes, but we have a use for one.

What I've done is setup time along the X axis and some other quantity (such
as a the minimum and maximum of some other, pertinent value, which we want
to search quickly) along the Y axis. These quite convieniently make a box
shape.

So once the above boxes are indexed, then the search query can construct any
box it wants and use the '&&' operator to test for overlaps. This is
working really well on the test data we have.

Now the search query wants the first N results (N is small, like 10-20
small) from the X axis (the time range) from the possibly hundreds of
matching rows.

What I dont know is how to express in the query that those are the rows I
want. If I was using normal float8 or int8 values, for example, then I'd use
something like

SELECT * FROM testtable WHERE starttime > 1108351025
ORDER BY starttime ASC LIMIT 10;

but I dont know how to express the equivalent for boxes.

Currently I ORDER BY one of the time components ('starttime') which makes up
one of the coordinates of the box. Of course postgres has no idea it makes
up one of the box coordinates, so it extracts all possible matches into a
temporary table and sorts that to get what I want. This is suboptimal.

Ive tried simply ordering by the boxes, which results in

ERROR: could not identify an ordering operator for type box

How can I do this, or is it a limitation of the geometric indexes?

.Guy

Browse pgsql-general by date

  From Date Subject
Next Message Neil Dugan 2005-02-14 03:47:20 possible bug with compound index.
Previous Message Neil Dugan 2005-02-14 00:08:50 Re: find next in an index