On Wed, Apr 06, 2005 at 06:52:35PM +0200, Arjen van der Meijden wrote:
Hi list,
I noticed on a forum a query taking a surprisingly large amount of time in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much better. To my surprise PostgreSQL was ten times worse on the same machine! And I don't understand why.
I don't really need this query to be fast since I don't use it, but the range-thing is not really an uncommon query I suppose. So I'm wondering why it is so slow and this may point to a wrong plan being chosen or generated.
That's the wrong index type for fast range queries. You really need something like GiST or rtree for that. I do something similar in production and queries are down at the millisecond level with the right index.
That may be, but since that table is only two pages the index would probably not be used even if it was rtree or GiST?
Btw, "access method "rtree" does not support multicolumn indexes", I'd need another way of storing it as well? Plus it doesn't support < and > so the query should be changed for the way ranges are checked.
I'm not sure if the dataset is really suitable for other range checks. It is a linear set of postal codes grouped by their number (range_from to range_till) into regions and the query basically joins the region to each records of a user table. Of course one could use lines on the x-axis and define the postal-code of a specific user as a point on one of those lines...
But nonetheless, /this/ query should be "not that slow" either, right?
Arjen
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
