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

От: Tom Lane
Тема: Re: Plan for relatively simple query seems to be very inefficient
Дата: ,
Msg-id: 28552.1112809334@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 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  (Arjen van der Meijden)
Список: 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, )

Arjen van der Meijden <> writes:
> 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.

Wrong index ... what you probably could use here is an index on
data_main.range, so that the query could run with postcodes as the
outer side.  I get such a plan by default with empty tables:

 Aggregate  (cost=99177.80..99177.80 rows=1 width=0)
   ->  Nested Loop  (cost=0.00..98021.80 rows=462400 width=0)
         ->  Seq Scan on postcodes p  (cost=0.00..30.40 rows=2040 width=4)
         ->  Index Scan using rangei on data_main dm  (cost=0.00..44.63 rows=227 width=2)
               Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till))

but I'm not sure if the planner would prefer it with the tables loaded
up.  (It might not be the right thing anyway ... but seems worth
trying.)

Given the relatively small size of the postcodes table, and the fact
that each data_main row seems to join to about one postcodes row,
it's possible that what the planner did for you was actually the
optimal thing anyhow.  I'm not sure that any range-capable index would
be faster than just scanning through 160 entries in memory ...

            regards, tom lane


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

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