Inaccurate row count estimation

From: "Vyacheslav Kalinin" <vka(at)mgcp(dot)com>
To: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Inaccurate row count estimation
Date: 2008-07-09 16:54:20
Message-ID: 9b1af80e0807090954v3b830462m67599a2d8f9b9047@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

Consider two tables:

contacts:
cid integer primary key,
pid integer not null,
cpid integer
...

pinfo:
pid integer,
...

pinfo is a parent table with two partitions pinfo_p00 and pinfo_p01, all
three have primary keys on pid and partitions have proper constraints
that guarantee pid uniqueness across them.

Now here's the part of the query:

select *
from contacts c
left join pinfo pi on (pi.pid = c.cpid)

QUERY PLAN
-> Nested Loop Left Join (cost=0.00..444.90 rows=1515
width=408) (actual time=0.108..5.561 rows=44 loops=1)
Join Filter: (pi.pid = c.cpid)
-> Index Scan using ix_contacts_pid on contacts
c (cost=0.00..14.84 rows=50 width=26) (actual time=0.038..0.425 rows=44
loops=1)
Index Cond: (pid = 167)
-> Append (cost=0.00..8.56 rows=3 width=386)
(actual time=0.067..0.090 rows=1 loops=44)
-> Index Scan using pk_pinfo on pinfo pi
(cost=0.00..1.15 rows=1 width=386) (actual time=0.008..0.008 rows=0
loops=44)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p00 on
pinfo_p00 pi (cost=0.00..3.23 rows=1 width=46) (actual time=0.011..0.014
rows=0 loops=44)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p01 on
pinfo_p01 pi (cost=0.00..4.19 rows=1 width=46) (actual time=0.011..0.014
rows=0 loops=44)
Index Cond: (pi.pid = c.cpid)

How come that outermost join expects 1515 rows given the row estimations of
the inner and outer nested loop's parts?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc Munro 2008-07-09 17:39:03 pgmemcache status
Previous Message Gwyneth Morrison 2008-07-09 16:20:09 Re: MSSQL to PostgreSQL