explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ec3G

Settings
# exclusive inclusive rows x rows loops node
1. 0.249 11.463 ↑ 2.6 141 1

Sort (cost=2,592.00..2,592.91 rows=365 width=700) (actual time=11.456..11.463 rows=141 loops=1)

  • Output: user_in_zone.uid, user_in_zone."includeSize", user_in_zone."includedFiles", user_in_zone."totaCFInZone", usr_toal.owner_id, usr_toal."totalSize", usr_toal."totalFiles", usr_toal."totaCFInPortal", usr_toal.name
  • Sort Key: usr_toal.name
  • Sort Method: quicksort Memory: 44kB
2.          

CTE t_current_users

3. 0.022 0.022 ↑ 1.0 154 1

Values Scan on "*VALUES*" (cost=0.00..1.93 rows=154 width=4) (actual time=0.001..0.022 rows=154 loops=1)

  • Output: "*VALUES*".column1
4.          

CTE t_remove_folders

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: '-1'::integer
6.          

CTE t_add_folders

7. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: '-1'::integer
8.          

CTE user_in_zone

9. 0.110 9.795 ↑ 1.1 154 1

GroupAggregate (cost=1,396.66..1,401.52 rows=162 width=104) (actual time=9.672..9.795 rows=154 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), sum((max("*SELECT* 1".included_size))), sum((max("*SELECT* 1".included_files))), sum((max("*SELECT* 1".total_lines)))
  • Group Key: (("*SELECT* 1".uid)::bigint)
10. 0.045 9.685 ↑ 1.1 154 1

Sort (cost=1,396.66..1,397.06 rows=162 width=80) (actual time=9.669..9.685 rows=154 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), (max("*SELECT* 1".included_size)), (max("*SELECT* 1".included_files)), (max("*SELECT* 1".total_lines))
  • Sort Key: (("*SELECT* 1".uid)::bigint)
  • Sort Method: quicksort Memory: 37kB
11. 0.051 9.640 ↑ 1.1 154 1

HashAggregate (cost=1,387.47..1,389.09 rows=162 width=80) (actual time=9.628..9.640 rows=154 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), (max("*SELECT* 1".included_size)), (max("*SELECT* 1".included_files)), (max("*SELECT* 1".total_lines))
  • Group Key: (("*SELECT* 1".uid)::bigint), (max("*SELECT* 1".included_size)), (max("*SELECT* 1".included_files)), (max("*SELECT* 1".total_lines))
12. 0.007 9.589 ↑ 1.1 154 1

Append (cost=1,363.87..1,385.85 rows=162 width=80) (actual time=9.503..9.589 rows=154 loops=1)

13. 0.106 9.565 ↑ 1.0 154 1

GroupAggregate (cost=1,363.87..1,367.47 rows=160 width=80) (actual time=9.503..9.565 rows=154 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), max("*SELECT* 1".included_size), max("*SELECT* 1".included_files), max("*SELECT* 1".total_lines)
  • Group Key: (("*SELECT* 1".uid)::bigint)
14. 0.035 9.459 ↑ 1.0 155 1

Sort (cost=1,363.87..1,364.27 rows=160 width=80) (actual time=9.450..9.459 rows=155 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), "*SELECT* 1".included_size, "*SELECT* 1".included_files, "*SELECT* 1".total_lines
  • Sort Key: (("*SELECT* 1".uid)::bigint)
  • Sort Method: quicksort Memory: 37kB
15. 0.060 9.424 ↑ 1.0 155 1

HashAggregate (cost=1,354.81..1,356.41 rows=160 width=80) (actual time=9.409..9.424 rows=155 loops=1)

  • Output: (("*SELECT* 1".uid)::bigint), "*SELECT* 1".included_size, "*SELECT* 1".included_files, "*SELECT* 1".total_lines
  • Group Key: (("*SELECT* 1".uid)::bigint), "*SELECT* 1".included_size, "*SELECT* 1".included_files, "*SELECT* 1".total_lines
16. 0.007 9.364 ↑ 1.0 155 1

Append (cost=1,314.54..1,353.21 rows=160 width=80) (actual time=8.997..9.364 rows=155 loops=1)

17. 0.016 9.313 ↑ 1.0 154 1

Subquery Scan on *SELECT* 1 (cost=1,314.54..1,321.65 rows=154 width=80) (actual time=8.997..9.313 rows=154 loops=1)

  • Output: "*SELECT* 1".uid, "*SELECT* 1".included_size, "*SELECT* 1".included_files, "*SELECT* 1".total_lines
18. 0.280 9.297 ↑ 1.0 154 1

GroupAggregate (cost=1,314.54..1,319.73 rows=154 width=76) (actual time=8.994..9.297 rows=154 loops=1)

  • Output: t_current_users.usr_id, sum(CASE WHEN (zone_ref.zone_id IS NULL) THEN '0'::bigint ELSE folders_statistics.folder_size END), sum(CASE WHEN (zone_ref.zone_id IS NULL) THEN '0'::bigint ELSE folders_statistics.total_files END), sum(CASE WHEN (zone_ref.zone_id IS NULL) THEN 0 ELSE 1 END)
  • Group Key: t_current_users.usr_id
19. 0.155 9.017 ↓ 2.0 460 1

Sort (cost=1,314.54..1,315.12 rows=230 width=28) (actual time=8.987..9.017 rows=460 loops=1)

  • Output: t_current_users.usr_id, zone_ref.zone_id, folders_statistics.folder_size, folders_statistics.total_files
  • Sort Key: t_current_users.usr_id
  • Sort Method: quicksort Memory: 60kB
20. 0.109 8.862 ↓ 2.0 460 1

Nested Loop Left Join (cost=5.29..1,305.52 rows=230 width=28) (actual time=0.056..8.862 rows=460 loops=1)

  • Output: t_current_users.usr_id, zone_ref.zone_id, folders_statistics.folder_size, folders_statistics.total_files
21. 1.558 8.293 ↓ 2.0 460 1

Hash Right Join (cost=5.00..1,193.78 rows=230 width=20) (actual time=0.052..8.293 rows=460 loops=1)

  • Output: t_current_users.usr_id, zone_ref.zone_id, zone_ref.cloud_folder_id
  • Hash Cond: (zone_ref.user_id = t_current_users.usr_id)
22. 6.714 6.714 ↓ 1.0 25,000 1

Seq Scan on public.zone_ref (cost=0.00..1,093.00 rows=24,927 width=24) (actual time=0.025..6.714 rows=25,000 loops=1)

  • Output: zone_ref.version, zone_ref.zone_id, zone_ref.user_id, zone_ref.cloud_folder_id, zone_ref.is_excluded, zone_ref.update_time
  • Filter: ((NOT zone_ref.is_excluded) AND (zone_ref.zone_id = 3))
  • Rows Removed by Filter: 25000
23. 0.013 0.021 ↑ 1.0 154 1

Hash (cost=3.08..3.08 rows=154 width=4) (actual time=0.021..0.021 rows=154 loops=1)

  • Output: t_current_users.usr_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
24. 0.008 0.008 ↑ 1.0 154 1

CTE Scan on t_current_users (cost=0.00..3.08 rows=154 width=4) (actual time=0.000..0.008 rows=154 loops=1)

  • Output: t_current_users.usr_id
25. 0.460 0.460 ↑ 1.0 1 460

Index Scan using folders_statistics_pkey on public.folders_statistics (cost=0.29..0.48 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=460)

  • Output: folders_statistics.folder_id, folders_statistics.folder_size, folders_statistics.total_files, folders_statistics.current_snapshot_id, folders_statistics.last_success_snapshot_id, folders_statistics.mode, folders_statistics.quota, folders_statistics.portal_id, folders_statistics.owner_id, folders_statistics.last_modify_server_id, folders_statistics.folder_group_id, folders_statistics."timestamp", folders_statistics.is_deleted, folders_statistics.base_object_type, folders_statistics.belongs_to_ad_user, folders_statistics.is_recoverable, folders_statistics.out_of_quota, folders_statistics.configured_quota
  • Index Cond: (zone_ref.cloud_folder_id = folders_statistics.folder_id)
  • Filter: (NOT folders_statistics.is_deleted)
26. 0.003 0.044 ↑ 6.0 1 1

GroupAggregate (cost=31.33..31.50 rows=6 width=80) (actual time=0.044..0.044 rows=1 loops=1)

  • Output: folders_statistics_1.owner_id, sum(folders_statistics_1.folder_size), sum(folders_statistics_1.total_files), count(*)
  • Group Key: folders_statistics_1.owner_id
27. 0.013 0.041 ↑ 2.0 3 1

Sort (cost=31.33..31.35 rows=6 width=24) (actual time=0.041..0.041 rows=3 loops=1)

  • Output: folders_statistics_1.owner_id, folders_statistics_1.folder_size, folders_statistics_1.total_files
  • Sort Key: folders_statistics_1.owner_id
  • Sort Method: quicksort Memory: 25kB
28. 0.008 0.028 ↑ 2.0 3 1

Bitmap Heap Scan on public.folders_statistics folders_statistics_1 (cost=8.64..31.25 rows=6 width=24) (actual time=0.025..0.028 rows=3 loops=1)

  • Output: folders_statistics_1.owner_id, folders_statistics_1.folder_size, folders_statistics_1.total_files
  • Recheck Cond: (folders_statistics_1.owner_id = ANY ('{-1,57}'::bigint[]))
  • Filter: (NOT folders_statistics_1.is_deleted)
  • Heap Blocks: exact=3
29. 0.020 0.020 ↑ 2.0 3 1

Bitmap Index Scan on folder_owner (cost=0.00..8.64 rows=6 width=0) (actual time=0.020..0.020 rows=3 loops=1)

  • Index Cond: ((folders_statistics_1.owner_id = ANY ('{-1,57}'::bigint[])) AND (folders_statistics_1.is_deleted = false))
30. 0.000 0.011 ↓ 0.0 0 1

GroupAggregate (cost=8.35..8.38 rows=1 width=80) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: folders_statistics_2.owner_id, sum((0 - folders_statistics_2.folder_size)), sum((0 - folders_statistics_2.total_files)), sum('-1'::integer)
  • Group Key: folders_statistics_2.owner_id
31. 0.003 0.011 ↓ 0.0 0 1

Sort (cost=8.35..8.35 rows=1 width=24) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: folders_statistics_2.owner_id, folders_statistics_2.folder_size, folders_statistics_2.total_files
  • Sort Key: folders_statistics_2.owner_id
  • Sort Method: quicksort Memory: 25kB
32. 0.002 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.29..8.34 rows=1 width=24) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: folders_statistics_2.owner_id, folders_statistics_2.folder_size, folders_statistics_2.total_files
33. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on t_remove_folders (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: t_remove_folders.f_id
34. 0.004 0.004 ↓ 0.0 0 1

Index Scan using folders_statistics_pkey on public.folders_statistics folders_statistics_2 (cost=0.29..8.31 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: folders_statistics_2.folder_id, folders_statistics_2.folder_size, folders_statistics_2.total_files, folders_statistics_2.current_snapshot_id, folders_statistics_2.last_success_snapshot_id, folders_statistics_2.mode, folders_statistics_2.quota, folders_statistics_2.portal_id, folders_statistics_2.owner_id, folders_statistics_2.last_modify_server_id, folders_statistics_2.folder_group_id, folders_statistics_2."timestamp", folders_statistics_2.is_deleted, folders_statistics_2.base_object_type, folders_statistics_2.belongs_to_ad_user, folders_statistics_2.is_recoverable, folders_statistics_2.out_of_quota, folders_statistics_2.configured_quota
  • Index Cond: (folders_statistics_2.folder_id = t_remove_folders.f_id)
  • Filter: (NOT folders_statistics_2.is_deleted)
35. 0.001 0.006 ↓ 0.0 0 1

GroupAggregate (cost=8.35..8.38 rows=1 width=80) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: folders_statistics_3.owner_id, sum(folders_statistics_3.folder_size), sum(folders_statistics_3.total_files), count(*)
  • Group Key: folders_statistics_3.owner_id
36. 0.002 0.005 ↓ 0.0 0 1

Sort (cost=8.35..8.35 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: folders_statistics_3.owner_id, folders_statistics_3.folder_size, folders_statistics_3.total_files
  • Sort Key: folders_statistics_3.owner_id
  • Sort Method: quicksort Memory: 25kB
37. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.29..8.34 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: folders_statistics_3.owner_id, folders_statistics_3.folder_size, folders_statistics_3.total_files
38. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on t_add_folders (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: t_add_folders.f_id
39. 0.001 0.001 ↓ 0.0 0 1

Index Scan using folders_statistics_pkey on public.folders_statistics folders_statistics_3 (cost=0.29..8.31 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: folders_statistics_3.folder_id, folders_statistics_3.folder_size, folders_statistics_3.total_files, folders_statistics_3.current_snapshot_id, folders_statistics_3.last_success_snapshot_id, folders_statistics_3.mode, folders_statistics_3.quota, folders_statistics_3.portal_id, folders_statistics_3.owner_id, folders_statistics_3.last_modify_server_id, folders_statistics_3.folder_group_id, folders_statistics_3."timestamp", folders_statistics_3.is_deleted, folders_statistics_3.base_object_type, folders_statistics_3.belongs_to_ad_user, folders_statistics_3.is_recoverable, folders_statistics_3.out_of_quota, folders_statistics_3.configured_quota
  • Index Cond: (folders_statistics_3.folder_id = t_add_folders.f_id)
  • Filter: (NOT folders_statistics_3.is_deleted)
40.          

CTE usr_toal

41. 0.334 1.289 ↑ 3.2 141 1

HashAggregate (cost=1,146.61..1,153.38 rows=451 width=102) (actual time=1.244..1.289 rows=141 loops=1)

  • Output: folders_statistics_4.owner_id, sum(CASE WHEN folders_statistics_4.is_deleted THEN '0'::bigint ELSE folders_statistics_4.folder_size END), sum(CASE WHEN folders_statistics_4.is_deleted THEN '0'::bigint ELSE folders_statistics_4.total_files END), count(*), base_objects.display_name
  • Group Key: folders_statistics_4.owner_id, base_objects.display_name
42. 0.135 0.955 ↑ 1.1 401 1

Nested Loop (cost=0.58..1,140.98 rows=451 width=47) (actual time=0.030..0.955 rows=401 loops=1)

  • Output: folders_statistics_4.owner_id, base_objects.display_name, folders_statistics_4.is_deleted, folders_statistics_4.folder_size, folders_statistics_4.total_files
  • Join Filter: (t_current_users_1.usr_id = folders_statistics_4.owner_id)
43. 0.006 0.361 ↑ 1.0 153 1

Nested Loop (cost=0.29..1,072.36 rows=154 width=34) (actual time=0.010..0.361 rows=153 loops=1)

  • Output: t_current_users_1.usr_id, base_objects.display_name, base_objects.uid
44. 0.047 0.047 ↑ 1.0 154 1

CTE Scan on t_current_users t_current_users_1 (cost=0.00..3.08 rows=154 width=4) (actual time=0.002..0.047 rows=154 loops=1)

  • Output: t_current_users_1.usr_id
45. 0.308 0.308 ↑ 1.0 1 154

Index Scan using base_objects_pkey on public.base_objects (cost=0.29..6.93 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=154)

  • Output: base_objects.uid, base_objects.name, base_objects.domain, base_objects.display_name, base_objects.type, base_objects.portal_id, base_objects.owner_id, base_objects.xml_field, base_objects.create_date, base_objects.modified_date, base_objects.is_deleted, base_objects.comment
  • Index Cond: (base_objects.uid = t_current_users_1.usr_id)
46. 0.459 0.459 ↑ 1.0 3 153

Index Scan using folder_owner on public.folders_statistics folders_statistics_4 (cost=0.29..0.41 rows=3 width=25) (actual time=0.002..0.003 rows=3 loops=153)

  • Output: folders_statistics_4.folder_id, folders_statistics_4.folder_size, folders_statistics_4.total_files, folders_statistics_4.current_snapshot_id, folders_statistics_4.last_success_snapshot_id, folders_statistics_4.mode, folders_statistics_4.quota, folders_statistics_4.portal_id, folders_statistics_4.owner_id, folders_statistics_4.last_modify_server_id, folders_statistics_4.folder_group_id, folders_statistics_4."timestamp", folders_statistics_4.is_deleted, folders_statistics_4.base_object_type, folders_statistics_4.belongs_to_ad_user, folders_statistics_4.is_recoverable, folders_statistics_4.out_of_quota, folders_statistics_4.configured_quota
  • Index Cond: (folders_statistics_4.owner_id = base_objects.uid)
47. 0.037 11.214 ↑ 2.6 141 1

Hash Join (cost=5.27..19.63 rows=365 width=700) (actual time=11.104..11.214 rows=141 loops=1)

  • Output: user_in_zone.uid, user_in_zone."includeSize", user_in_zone."includedFiles", user_in_zone."totaCFInZone", usr_toal.owner_id, usr_toal."totalSize", usr_toal."totalFiles", usr_toal."totaCFInPortal", usr_toal.name
  • Hash Cond: (usr_toal.owner_id = user_in_zone.uid)
48. 1.326 1.326 ↑ 3.2 141 1

CTE Scan on usr_toal (cost=0.00..9.02 rows=451 width=596) (actual time=1.246..1.326 rows=141 loops=1)

  • Output: usr_toal.owner_id, usr_toal."totalSize", usr_toal."totalFiles", usr_toal."totaCFInPortal", usr_toal.name
49. 0.025 9.851 ↑ 1.1 154 1

Hash (cost=3.24..3.24 rows=162 width=104) (actual time=9.851..9.851 rows=154 loops=1)

  • Output: user_in_zone.uid, user_in_zone."includeSize", user_in_zone."includedFiles", user_in_zone."totaCFInZone
  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
50. 9.826 9.826 ↑ 1.1 154 1

CTE Scan on user_in_zone (cost=0.00..3.24 rows=162 width=104) (actual time=9.674..9.826 rows=154 loops=1)

  • Output: user_in_zone.uid, user_in_zone."includeSize", user_in_zone."includedFiles", user_in_zone."totaCFInZone
Planning time : 1.491 ms