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 '%@%')))
) 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 ~~* '%@%'::text)
Total runtime: 8573.237 ms
