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?
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 |