Re: Very specialised query

Список
Период
Сортировка
От Matthew Wakeling
Тема Re: Very specialised query
Дата
Msg-id alpine.DEB.2.00.0903301119120.21772@aragorn.flymine.org
обсуждение исходный текст
Ответ на Re: Very specialised query  (Tom Lane)
Список pgsql-performance
Дерево обсуждения
Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Kevin Grittner", )
 Re: Very specialised query  (Tom Lane, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Tom Lane, )
 Re: Very specialised query  (Віталій Тимчишин, )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  (Tom Lane, )
    Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Віталій Тимчишин, )
      Re: Very specialised query  (Matthew Wakeling, )
       Re: Very specialised query  (Віталій Тимчишин, )
        Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Dimitri Fontaine, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  ("Marc Mamin", )
    Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Віталій Тимчишин, )
   Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Matthew Wakeling, )
      Re: Very specialised query  (Craig Ringer, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
On Fri, 27 Mar 2009, Tom Lane wrote:
>> Notice the two different index conditions:
>>      (l1.end > l2.start) AND (l1.start < l2.start)  - "between"
>>      (l1.end > l2.start) AND (l1.start >= l2.start) - open-ended
>> Both have a cost of (cost=0.00..123.10 rows=4809 width=12)

> Currently the planner only notices that for a range check that involves
> comparisons of the same variable expression to two constants (or
> pseudoconstants anyway).  In principle it might be reasonable to have a
> heuristic that reduces the estimated selectivity in the example above,
> but it looks to me like it'd make clauselist_selectivity() a lot slower
> and more complicated.  When you see (l1.end > l2.start), how do you know
> which variable to try to match up against others?  And if you try to
> match both, what do you do when you get matches for both?

Those two index conditions are on an index scan on the field l2.start.
Therefore, I would expect to only have to take any notice of l2.start when
working out selectivity on a range check for this particular plan. When
there is an index scan on a different field, then try and match that one
up instead.

Matthew

--

В списке pgsql-performance по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: Re: Very specialised query
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance