Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group