On Mon, Apr 13, 2009 at 1:36 PM, Paul Wehr
<sf_psql_general@informationsoftworks.com> wrote:
>
> 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?
Have you tried an index on test1.c?