Re: Query planner and foreign key constraints

2008/12/30 Christian Schröder <cs(at)deriva(dot)de>

> Hi list,
> in our PostgreSQL 8.2.9 database I have these tables:
>
> create table table1 (
> key1 char(12),
> key2 integer,
> primary key (key1, key2)
> );
>
> create table table2 (
> key1 char(12),
> key2 integer,
> key3 varchar(20),
> primary key (key1, key2, key3),
> foreign key (key1, key2) references table1 (key1, key2)
> );
>
> Table1 has 896576 rows. Table2 has 1630788 rows. The statistics target of
> the columns key1 and key2 in both tables has been set to 1000. Both tables
> have been analyzed.
> When I join both tables using key1 and key2 there will be exactly 1630788
> rows because for each row in table2 there *must* exist a row in table1. But
> the query planner doesn't think so:
>
> # explain analyze select * from table1 inner join table2 using (key1,
> key2);
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=0.00..94916.58 rows=39560 width=44) (actual
> time=0.103..7105.960 rows=1630788 loops=1)
> Merge Cond: ((table1.key1 = table2.key1) AND (table1.key2 = table2.key2))
> -> Index Scan using table1_pkey on table1 (cost=0.00..22677.65
> rows=896576 width=20) (actual time=0.038..689.059 rows=896576 loops=1)
> -> Index Scan using table2_pkey on table2 (cost=0.00..59213.16
> rows=1630788 width=44) (actual time=0.049..1108.220 rows=1630788 loops=1)
> Total runtime: 7525.492 ms
> (5 rows)
>
> You can also find the query plan at
> http://explain-analyze.info/query_plans/2648-query-plan-1371.
>
> What can I do to make the query planner realize that the join will have
> 1630788 rows? This join is part of a view which I then use in other joins
> and this wrong assumption leads to really bad performance.
>

just a guess, but - did you try to declare NOT NULL on FK columns?

your assumption that "for each row in table2 there *must* exist a row in
table1" will be enforced then.

maybe the planner will make use of this ...

--
Filip Rembiałkowski