Re: Query planner and foreign key constraints

Christian Schröder wrote:
> 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.
I have not yet found any solution. My queries still take several minutes
to complete. :-(
No ideas at all?

Regards,
    Christian

--
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer