From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Equivalent queries produce different plans
Date: Tue, 10 Jul 2007 21:25:03 -0400
Message-ID: <13642.1184117103@sss.pgh.pa.us> (raw)
In-Reply-To: <469429FD.6080504@emolecules.com>
References: <46898AB3.7090409@emolecules.com>
<87hcom5p41.fsf@oxford.xeocode.com>
<469429FD.6080504@emolecules.com>
Craig James <craig_james@emolecules.com> writes:
> The two queries below produce different plans.
> select r.version_id, r.row_num, m.molkeys from my_rownum r
> join my_molkeys m on (r.version_id = m.version_id)
> where r.version_id >= 3200000
> and r.version_id < 3300000
> order by r.version_id;
> select r.version_id, r.row_num, m.molkeys from my_rownum r
> join my_molkeys m on (r.version_id = m.version_id)
> where r.version_id >= 3200000
> and r.version_id < 3300000
> and m.version_id >= 3200000
> and m.version_id < 3300000
> order by r.version_id;
Yeah, the planner does not make any attempt to infer implied
inequalities, so it will not generate the last two clauses for you.
There is machinery in there to infer implied *equalities*, which
is cheaper (fewer operators to consider) and much more useful across
typical queries such as multiway joins on the same keys. I'm pretty
dubious that it'd be worth the cycles to search for implied
inequalities.
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, craig_james@emolecules.com
Subject: Re: Equivalent queries produce different plans
In-Reply-To: <13642.1184117103@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