Skip site navigation (1) Skip section navigation (2)

Re: Very specialised query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Віталій Тимчишин <tivv00(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very specialised query
Date: 2009-03-27 18:43:13
Message-ID: 2298.1238179393@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Matthew Wakeling <matthew(at)flymine(dot)org> 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

In response to

Responses

pgsql-performance by date

Next:From: Dimitri FontaineDate: 2009-03-27 20:07:56
Subject: Re: Very specialised query
Previous:From: JeffDate: 2009-03-27 17:54:37
Subject: Re: I have a fusion IO drive available for testing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group