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: 28760.1112810943@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Plan for relatively simple query seems to be very inefficient  ("Dave Held")
Список: 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, )

"Dave Held" <> writes:
> My completely amateur guess is that the planner is able to use
> Merge Join and Hash Join on your contrived queries because you
> are only trying to join one field to a single value (i.e.:
> operator=).  But the BETWEEN clause is what forces the Nested
> Loop.  You can see that here:

Yeah --- both merge and hash join are only usable for equality joins.
(Thinking about it, it seems possible that mergejoin could be extended
to work for range joins, but we're certainly far from being able to
do that today.)  So the basic alternatives the planner has are nestloops
with either postcode on the outside, or data_main on the outside.  The
postcode-on-the-outside case would be plausible with an index on
data_main.range, but Arjen didn't have one.  The data_main-on-the-outside
case could only use an index if the index was range-query-capable, which
a 2-column btree index isn't.  Given the small size of the postcodes
table it's not real clear that an index probe would be much of a win
anyway over a simple sequential scan.

Comparing the nestloop case to the hash case does make one think that
there's an awful lot of overhead somewhere, though.  Two int2
comparisons ought not take very long :-(.  Arjen, are you interested
in getting a gprof profile of what the backend is doing in the nestloop
-with-materialize plan?  Or if you don't want to mess with it, please
send me the data off-list and I'll run a profile.

            regards, tom lane


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

От: Neil Conway
Дата:
Сообщение: Re: Tweaking a C Function I wrote
От: Alex Turner
Дата:
Сообщение: Re: How to improve db performance with $7K?