Re: BUMP: Using foreign key constraint to eliminate unnecessary joins in view

On Mon, Apr 13, 2009 at 1:36 PM, Paul Wehr
<sf_psql_general(at)informationsoftworks(dot)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(at)informationsoftworks(dot)com>
> To: pgsql-general(at)postgresql(dot)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?