From: Robert Klemme <shortcutter@googlemail.com>
To: pgsql-performance@postgresql.org
Subject: Re: need to repeat the same condition on joined tables in order to choose the proper plan
Date: Tue, 14 Jun 2011 22:21:17 +0200
Message-ID: <it8fs4$vlh$1@dough.gmane.org> (raw)
In-Reply-To: <676.1308068945@sss.pgh.pa.us>
References: <BANLkTikSqgoucZkO0F3v8pHCopXjR=+NYg@mail.gmail.com>
<676.1308068945@sss.pgh.pa.us>
On 14.06.2011 18:29, Tom Lane wrote:
> Svetlin Manavski<svetlin.manavski@gmail.com> writes:
>> I am really surprised to see that the planner needs me to explicitly specify
>> the same condition twice like this:
>
>> SD.detectorid = SS.detectorid
>> and SD.sessionid = SS.id
>> and SD.detectorid = 1
>> and SD.sessionid>= 1001000000000::INT8 and SD.sessionid<=
>> 2001000000000::INT8
>> and SS.detectorid = 1
>> and SS.id>= 1001000000000::INT8 and SS.id<= 2001000000000::INT8
>
> The planner does infer implied equalities, eg, given A = B and B = C
> it will figure out that A = C. What you are asking is for it to derive
> inequalities, eg infer A< C from A = B and B< C. That would be
> considerably more work for considerably less reward, since the sort of
> situation where this is helpful doesn't come up very often. On balance
> I don't believe it's a good thing for us to do: I think it would make
> PG slower on average because on most queries it would just waste time
> looking for this sort of situation.
>
> (In this example, the SS.detectorid = 1 clause is in fact unnecessary,
> since the planner will infer it from SD.detectorid = SS.detectorid and
> SD.detectorid = 1. But it won't infer the range conditions on SS.id
> from the range conditions on SD.sessionid or vice versa.)
Is that the same for IN? Would it help in this particular case to use a
and SS.id in (select ... where ... > and ... < ...) or with a CTE?
Kind regards
robert
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: shortcutter@googlemail.com
Subject: Re: need to repeat the same condition on joined tables in order to choose the proper plan
In-Reply-To: <it8fs4$vlh$1@dough.gmane.org>
* 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