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