2008/12/30 Christian Schröder <c...@deriva.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
