Re: Planner matching constants across tables in a join - Mailing list pgsql-performance

From Greg Stark
Subject Re: Planner matching constants across tables in a join
Date
Msg-id 87bs0q9e8l.fsf@stark.dyndns.tv
Whole thread Raw
In response to Planner matching constants across tables in a join  (Richard Huxton <dev@archonet.com>)
Responses Re: Planner matching constants across tables in a join  (Richard Huxton <dev@archonet.com>)
Re: Planner matching constants across tables in a join  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Richard Huxton <dev@archonet.com> writes:

> I know this has been covered on one of the lists in the past, but I'm damned
> if I can find the keywords to locate it.
>
> If I join two tables with a comparison to a constant on one, why can't the
> planner see that the comparison applies to both tables:

It sure does. Postgres does an impressive job of tracing equality clauses
around for just this purpose.

> SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1;
>
> runs much slower than
>
> SELECT a.id FROM a JOIN b ON a.id=b.id WHERE a.id=1 AND b.id=1;

Really? They produce virtually the same plan for me.

Why do you think it'll run slower?
What query are you actually finding slow?

--
greg

pgsql-performance by date:

Previous
From: Richard Huxton
Date:
Subject: Planner matching constants across tables in a join
Next
From: Neil Conway
Date:
Subject: Re: OIDs as keys