Query planner and foreign key constraints

From: Christian Schröder <cs(at)deriva(dot)de>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Query planner and foreign key constraints
Date: 2008-12-29 23:13:13
Message-ID: 49595989.2050906@deriva.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Filip Rembiałkowski 2008-12-30 02:48:43 Re: Query planner and foreign key constraints
Previous Message Adrian Klaver 2008-12-29 21:27:03 Re: How can the error log be searched?