A last try on this question. In the absence of any response, I'll assume
the postgresql query optimizer simply doesn't take foreign keys into
account in this way.
-------- Original Message --------
Subject: Using foreign key constraint to eliminate unnecessary joins in
view
Date: Sun, 29 Mar 2009 14:00:53 -0400
From: Paul Wehr <sf_psql_general@informationsoftworks.com>
To: pgsql-general@postgresql.org
I could be wrong, but I would swear I was once able to set up my foreign
key constraints so that the optimizer would not bother looking up rows in
tables that did not contribute to the result set. This is useful because I
can set up a single view that joins all the related tables together, but
can still get the performance of a view that only includes the tables I'm
interested in at the time.
Here is a simple example of what I mean:
------ begin example
create table test1 (
a serial not null,
b text,
primary key (a)
);
create table test2 (
c text,
d integer,
constraint test2_fk foreign key (d) references test1(a) not deferrable
;
insert into test1 (b) values ('test1');
insert into test1 (b) values ('test2');
insert into test2 values ('hey',1);
insert into test2 values ('stuff',1);
insert into test2 values ('thing',2);
explain
select c
from test2, test1
where test1.a = test2.d
------- end example
Explain plan from postgresql 8.3.7:
Hash Join (cost=37.67..76.89 rows=1230 width=32)
Hash Cond: (test2.d = test1.a)
-> Seq Scan on test2 (cost=0.00..22.30 rows=1230 width=36)
-> Hash (cost=22.30..22.30 rows=1230 width=4)
-> Seq Scan on test1 (cost=0.00..22.30 rows=1230 width=4)
I am looking for a way to get the plan to show only a seq scan of test2,
since test1 does not contribute any columns, and we know from the
not-deferrable-not-null-primary-key that there will always be exactly one
match.
Am I just missing something obvious, or does postgresql (currently) not do
that?
