Postgres Professional
en

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

От: Arjen van der Meijden
Тема: Re: Plan for relatively simple query seems to be very inefficient
Дата: ,
Msg-id: 42541F0D.9010009@vulcanus.its.tudelft.nl
(см: обсуждение, исходный текст)
Ответ на: Re: Plan for relatively simple query seems to be very inefficient  (Steve Atkins)
Список: pgsql-performance

Скрыть дерево обсуждения

Plan for relatively simple query seems to be very inefficient  (Arjen van der Meijden, )
 Re: Plan for relatively simple query seems to be very inefficient  (Steve Atkins, )
  Re: Plan for relatively simple query seems to be very inefficient  (Arjen van der Meijden, )
 Re: Plan for relatively simple query seems to be very inefficient  (Tom Lane, )
  Re: Plan for relatively simple query seems to be very inefficient  (Arjen van der Meijden, )
   Re: Plan for relatively simple query seems to be very inefficient  (Tom Lane, )
    Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Tom Lane, )
     Re: [HACKERS] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  ("Jim C. Nasby", )
      Re: [HACKERS] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Tom Lane, )
       Re: [HACKERS] Recognizing range constraints (was Re: Plan  (John A Meinel, )
        Re: [HACKERS] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Tom Lane, )
       Re: [HACKERS] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  ("Jim C. Nasby", )
     Re: Recognizing range constraints (was Re: Plan for  (Simon Riggs, )
     Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Bruno Wolff III, )
      Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Tom Lane, )
       Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Mischa, )
        Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (Tom Lane, )
         Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)  (, )
 Re: Plan for relatively simple query seems to be very inefficient  ("Dave Held", )
  Re: Plan for relatively simple query seems to be very inefficient  (Tom Lane, )
  Re: Plan for relatively simple query seems to be very inefficient  (Tom Lane, )
 Re: Plan for relatively simple query seems to be very inefficient  (Mischa, ) 
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

В списке pgsql-performance по дате сообщения:

Предыдущее:
От: Simon Riggs
Дата:
Сообщение: Re: Recognizing range constraints (was Re: Plan for
Следующее:
От: Neil Conway
Дата:
Сообщение: Re: Tweaking a C Function I wrote