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

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?

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?

On Mon, Apr 13, 2009 at 2:36 PM, Paul Wehr
<sf_psql_general@informationsoftworks.com> wrote:
>
> 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?
>

postgres will look at all tables involved, i think there is work to
make it smart enough to make what you describe but that's in the
future...


--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Paul Wehr <sf_psql_general@informationsoftworks.com> writes:
> 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.

It does not, and never has, and yes there's a TODO item about that.

            regards, tom lane