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