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

Re: Planner matching constants across tables in a join

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Planner matching constants across tables in a join
Date: 2003-03-05 12:42:18
Message-ID: 87bs0q9e8l.fsf@stark.dyndns.tv (view raw or flat)
Thread:
Lists: pgsql-performance
Richard Huxton <dev(at)archonet(dot)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


In response to

Responses

pgsql-performance by date

Next:From: Neil ConwayDate: 2003-03-05 13:54:58
Subject: Re: OIDs as keys
Previous:From: Richard HuxtonDate: 2003-03-05 11:13:14
Subject: Planner matching constants across tables in a join

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