Why does the number of rows are different in actual and estimated.

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Why does the number of rows are different in actual and estimated.
Date: 2012-12-13 22:12:22
Message-ID: CAGoODpdaGPEOyAPkWvLD=0xfQ=k-a8B_fT=Tb6QJ0uNrDv=Hvg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Why does the number of rows are different in actual and estimated?
The default_statistics_target is set to 100.

explain analyze
select *
FROM (
SELECT
entity.id AS "con_s_id", entity.setype AS "con_s_setype" ,
con_details.salutation AS "con_s_salutationtype", con_details.firstname AS
"con_s_firstname",
con_details.phone AS "con_s_phone", con_details.lastname AS
"con_s_lastname",
con_details.accountid AS "con_s_account_id_entityid", con_details.mobile AS
"con_s_mobile",
con_details.title AS "con_s_title", con_details.donotcall AS
"con_s_donotcall",
con_details.fax AS "con_s_fax", con_details.department AS
"con_s_department",
con_details.email AS "con_s_email", con_details.yahooid AS "con_s_yahooid",
con_details.emailoptout AS "con_s_emailoptout", con_details.reportsto AS
"con_s_con__id_entityid",
con_details.reference AS "con_s_reference", entity.smownerid AS
"con_s_assigned_user_id_entityid",
CASE WHEN entity.owner_type='U' THEN users.user_name ELSE groups.groupname
END AS "con_s_assigned_user_id_name",
CASE WHEN entity.owner_type='U' THEN users.first_name || ' ' ||
users.last_name ELSE groups.groupname END AS "con_s_assigned_user_id",
CASE WHEN entity.owner_type='U' THEN 'Users' ELSE 'Groups' END AS
"con_s_assigned_user_id_linkmodule",
entity.modifiedtime AS "con_s_modifiedtime", con_details.notify_owner AS
"con_s_notify_owner",
entity.createdtime AS "con_s_createdtime", entity.description AS
"con_s_description",
con_details.imagename AS "con_s_imagename"
FROM con_details
INNER JOIN entity ON con_details.con_id=entity.id
LEFT JOIN groups ON groups.groupid = entity.smownerid
LEFT join users ON entity.smownerid= users.id
WHERE entity.setype='con_s' AND entity.deleted=0
AND (((con_details.email ILIKE '%(at)%')))
) con_base
INNER JOIN con_scf ON con_s_base."con_s_id"=con_scf.con_id
INNER JOIN con_subdetails ON
con_s_base."con_s_id"=con_subdetails.con_subscriptionid
INNER JOIN customerdetails ON
con_s_base."con_s_id"=customerdetails.customerid
INNER JOIN con_address ON con_s_base."con_s_id"=con_address.con_addressid

Nested Loop (cost=18560.97..26864.83 rows=24871 width=535) (actual
time=1335.157..8492.414 rows=157953 loops=1)
-> Hash Left Join (cost=18560.97..26518.91 rows=116 width=454) (actual
time=1335.117..6996.585 rows=205418 loops=1)
Hash Cond: (entity.smownerid = users.id)
-> Hash Left Join (cost=18547.22..26503.57 rows=116 width=419)
(actual time=1334.354..6671.442 rows=205418 loops=1)
Hash Cond: (entity.smownerid = groups.groupid)
-> Nested Loop (cost=18546.83..26502.72 rows=116
width=398) (actual time=1334.314..6385.664 rows=205418 loops=1)
-> Nested Loop (cost=18546.83..26273.40 rows=774
width=319) (actual time=1334.272..5025.175 rows=205418 loops=1)
-> Hash Join (cost=18546.83..24775.02
rows=5213 width=273) (actual time=1334.238..3666.748 rows=205420 loops=1)
Hash Cond:
(con_subdetails.con_subscriptionid = entity.id)
-> Index Scan using con_subdetails_pkey
on con_subdetails (cost=0.00..4953.41 rows=326040 width=29) (actual
time=0.019..350
.736 rows=327328 loops=1)
-> Hash (cost=18115.71..18115.71
rows=34489 width=244) (actual time=1334.147..1334.147 rows=205420 loops=1)
Buckets: 4096 Batches: 1 Memory
Usage: 19417kB
-> Hash Join
(cost=9337.97..18115.71 rows=34489 width=244) (actual
time=418.054..1156.453 rows=205420 loops=1)
Hash Cond:
(customerdetails.customerid = entity.id)
-> Seq Scan on
customerdetails (cost=0.00..4752.46 rows=327146 width=13) (actual
time=0.021..176.389 rows=327328 loops=1)
-> Hash
(cost=6495.65..6495.65 rows=227386 width=231) (actual
time=417.839..417.839 rows=205420 loops=1)
Buckets: 32768 Batches:
1 Memory Usage: 16056kB
-> Index Scan using
entity_setype_idx on entity (cost=0.00..6495.65 rows=227386 width=231)
(actual time=0.033..2
53.880 rows=205420 loops=1)
Index Cond:
((setype)::text = 'con_s'::text)
-> Index Scan using con_address_pkey on
con_address (cost=0.00..0.27 rows=1 width=46) (actual time=0.003..0.004
rows=1 loops=2054
20)
Index Cond: (con_addressid = entity.id)
-> Index Scan using con_scf_pkey on con_scf
(cost=0.00..0.28 rows=1 width=79) (actual time=0.003..0.004 rows=1
loops=205418)
Index Cond: (con_id = entity.id)
-> Hash (cost=0.34..0.34 rows=4 width=25) (actual
time=0.016..0.016 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Index Scan using groups_pkey on groups
(cost=0.00..0.34 rows=4 width=25) (actual time=0.008..0.012 rows=4 loops=1)
-> Hash (cost=9.00..9.00 rows=380 width=39) (actual
time=0.746..0.746 rows=380 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Index Scan using users_pkey on users (cost=0.00..9.00
rows=380 width=39) (actual time=0.014..0.440 rows=380 loops=1)
-> Index Scan using con_details_pkey on con_details (cost=0.00..0.29
rows=1 width=85) (actual time=0.004..0.004 rows=1 loops=205418)
Index Cond: (con_id = entity.id)
Filter: ((email)::text ~~* '%(at)%'::text)
Total runtime: 8573.237 ms

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Dunstan 2012-12-13 22:36:26 Re: Why does the number of rows are different in actual and estimated.
Previous Message Osborn, Jeff 2012-12-13 20:57:21 Re: Occasional timeouts on TRUNCATE and simple INSERTs