Why does the number of rows are different in actual and estimated. - Mailing list 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 '%@%'))) 

) 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