Re: Very specialised query

From: Tom Lane
Subject: Re: Very specialised query
Date: ,
Msg-id: 2298.1238179393@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Very specialised query  (Matthew Wakeling)
Responses: Re: Very specialised query  (Matthew Wakeling)
Re: Very specialised query  (Matthew Wakeling)
List: pgsql-performance

Tree view

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 by date:

From: "Marc Mamin"
Date:
Subject: Re: Very specialised query
From: Josh Berkus
Date:
Subject: Re: Proposal of tunable fix for scalability of 8.4