From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Віталій Тимчишин <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very specialised query
Date: Fri, 27 Mar 2009 14:43:13 -0400
Message-ID: <2298.1238179393@sss.pgh.pa.us> (raw)
In-Reply-To: <alpine.DEB.2.00.0903271708080.21772@aragorn.flymine.org>
References: <alpine.DEB.2.00.0903261313290.21772@aragorn.flymine.org>
<331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com>
<alpine.DEB.2.00.0903271708080.21772@aragorn.flymine.org>
Matthew Wakeling <matthew@flymine.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, matthew@flymine.org, tivv00@gmail.com
Subject: Re: Very specialised query
In-Reply-To: <2298.1238179393@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox