Re: Very specialised query

Список
Период
Сортировка
От Tom Lane
Тема Re: Very specialised query
Дата
Msg-id 2298.1238179393@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Very specialised query  (Matthew Wakeling)
Ответы Re: Very specialised query  (Matthew Wakeling)
Re: Very specialised query  (Matthew Wakeling)
Список 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, )
Matthew Wakeling <> writes:
> 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)

> Postgres estimates these two index scans to be equivalent in cost, where
> they are actually vastly different in real cost. Shouldn't Postgres favour
> a "between" index scan over an open-ended one?

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?

            regards, tom lane

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

Предыдущее
От: "Marc Mamin"
Дата:
Сообщение: Re: Very specialised query
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: Proposal of tunable fix for scalability of 8.4