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

Planner matching constants across tables in a join

From: Richard Huxton <dev(at)archonet(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Planner matching constants across tables in a join
Date: 2003-03-05 11:13:14
Message-ID: 200303051113.14320.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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:

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;

It's not a real problem since it's easy to work around, but I was wondering 
what the difficulties are for the planner in seeing that query 1 is the same 
as query 2. Note that it doesn't seem related to JOIN forcing the planner's 
hand, the same applies just using WHERE a.id=b.id

-- 
  Richard Huxton

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2003-03-05 12:42:18
Subject: Re: Planner matching constants across tables in a join
Previous:From: Paul McKayDate: 2003-03-05 10:27:27
Subject: Re: Slow query performance on large table

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