On Sun, Nov 22, 2009 at 10:23:29AM +0000, Clive Page wrote:
> Thanks for your reply. I should have said that I was using v8.1. After
> I posted my question, I retried with
> CREATE INDEX ... USING GIST(errbox box_ops)
> and left it to run overnight. The query using the index, which finds
> overlaps between rectangular boxes using the && operator, took 10228
> seconds, whereas using RTREES in v8.1 it took around 50 seconds. I have
> several such queries to do, and cannot afford to wait for hours.
If it really is that much slower, then it's a bug and should be fixed.
However, you have not provided not nearly enough information to work
out what the problem is. Could you show the EXPLAIN output from 8.1 and
8.4 at least so we have some handle on what your problem is.
PostgreSQL is used extensively for geometric queries, see postgis. They
abandoned rtree a while back because the GiST rtree support was better,
see
http://postgis.refractions.net/documentation/manual-1.3/ch03.html#id2570697
You are AFAICR the first person to have a problem is this area, but if
you can't take the few minutes needed to run EXPLAIN on before and
after then there is zero chance of it being fixed either.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.