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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux