Re: Plan for relatively simple query seems to be very inefficient

On 6-4-2005 19:04, Steve Atkins wrote:
> 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