explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1wKG

Settings
# exclusive inclusive rows x rows loops node
1. 319.898 6,690.447 ↑ 1.0 40 1

GroupAggregate (cost=3,157,343.89..3,166,428.18 rows=40 width=76) (actual time=6,154.007..6,690.447 rows=40 loops=1)

  • Group Key: cte0.c1, cte0.c2, cte0.c3
2.          

CTE filtered_users_cte

3. 0.287 0.641 ↓ 1.3 387 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=90.42..6,312.73 rows=293 width=37) (actual time=0.427..0.641 rows=387 loops=1)

  • Recheck Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR $2))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=45
4.          

Initplan (for Bitmap Heap Scan)

5. 0.007 0.032 ↑ 1.0 1 1

Bitmap Heap Scan on t773_userdim (cost=4.97..53.21 rows=1 width=0) (actual time=0.032..0.032 rows=1 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (sid = sid) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
6. 0.025 0.025 ↑ 1.9 36 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.025..0.025 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
7. 0.047 0.047 ↑ 1.0 388 1

Bitmap Index Scan on t773_userdim_sidendstampunique (cost=0.00..37.14 rows=391 width=0) (actual time=0.047..0.047 rows=388 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim.sid = t786_userrolehierarchy.c4812_user_role_id)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim (cost=0.00..7.35 rows=29 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
11. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t773_userdim_1.sid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
13. 0.085 0.275 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.095..0.275 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
14. 0.110 0.146 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_1 (cost=16.01..88.47 rows=364 width=8) (actual time=0.047..0.146 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
15. 0.036 0.036 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.036..0.036 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
16. 0.008 0.044 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.044..0.044 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.036 0.036 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.005..0.036 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
18.          

CTE primarysortedusercte_meeting_current

19. 0.004 125.189 ↑ 1.0 40 1

Limit (cost=2,241,257.35..2,241,257.45 rows=40 width=76) (actual time=125.185..125.189 rows=40 loops=1)

20.          

CTE innerprimarysortedusercte

21. 15.057 124.119 ↑ 1.5 137 1

GroupAggregate (cost=2,240,705.44..2,241,224.74 rows=200 width=12) (actual time=103.243..124.119 rows=137 loops=1)

  • Group Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
22. 43.818 109.062 ↓ 1.2 79,692 1

Sort (cost=2,240,705.44..2,240,877.88 rows=68,973 width=8) (actual time=102.618..109.062 rows=79,692 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
  • Sort Method: external merge Disk: 1,416kB
23. 64.987 65.244 ↓ 1.2 79,692 1

Index Scan using idx_accountid_sentha on t2273_meetingactivityaccountfact (cost=0.42..2,235,162.17 rows=68,973 width=8) (actual time=0.292..65.244 rows=79,692 loops=1)

  • Index Cond: ((c8986_activitydate >= '1561532400000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))
  • Rows Removed by Filter: 8,346
24.          

SubPlan (for Index Scan)

25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim_2.sid = t786_userrolehierarchy_2.c4812_user_role_id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_2 (cost=0.00..7.35 rows=29 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy t786_userrolehierarchy_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
29. 0.071 0.207 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.083..0.207 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
30. 0.068 0.103 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_3 (cost=16.01..88.47 rows=364 width=8) (actual time=0.042..0.103 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
31. 0.035 0.035 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.035..0.035 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
32. 0.007 0.033 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.033..0.033 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
33. 0.026 0.026 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.002..0.026 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim t773_userdim_2 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
35. 0.035 0.050 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=68 width=4) (actual time=0.020..0.050 rows=27 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=23
36. 0.015 0.015 ↑ 1.9 36 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.015..0.015 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
37. 0.168 125.185 ↑ 7.3 40 1

Sort (cost=32.61..33.34 rows=293 width=76) (actual time=125.184..125.185 rows=40 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c1 IS NULL) THEN '0'::bigint ELSE cte0_1.c1 END) DESC, cte1.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 31kB
38. 0.082 125.017 ↓ 1.3 387 1

Hash Left Join (cost=6.50..23.35 rows=293 width=76) (actual time=124.628..125.017 rows=387 loops=1)

  • Hash Cond: (cte1.c1 = cte0_1.c2)
39. 0.747 0.747 ↓ 1.3 387 1

CTE Scan on filtered_users_cte cte1 (cost=0.00..5.86 rows=293 width=68) (actual time=0.428..0.747 rows=387 loops=1)

40. 0.033 124.188 ↑ 1.5 137 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=124.188..124.188 rows=137 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
41. 124.155 124.155 ↑ 1.5 137 1

CTE Scan on innerprimarysortedusercte cte0_1 (cost=0.00..4.00 rows=200 width=12) (actual time=103.245..124.155 rows=137 loops=1)

42. 1,723.585 6,370.549 ↓ 1.4 1,025,810 1

Sort (cost=909,773.71..911,590.49 rows=726,711 width=72) (actual time=6,126.076..6,370.549 rows=1,025,810 loops=1)

  • Sort Key: cte0.c1, cte0.c2, cte0.c3
  • Sort Method: external merge Disk: 53,928kB
43. 488.140 4,646.964 ↓ 1.4 1,025,810 1

Hash Right Join (cost=1.30..779,405.68 rows=726,711 width=72) (actual time=125.225..4,646.964 rows=1,025,810 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid = cte0.c2)
44. 4,033.613 4,033.613 ↑ 1.0 6,135,534 1

Seq Scan on t2608_emailactivityaccountfact_copy_sentha (cost=0.00..748,499.29 rows=6,303,462 width=8) (actual time=0.008..4,033.613 rows=6,135,534 loops=1)

  • Filter: ((NOT deleted) AND c9660_isinternalinitiated AND c9657_isshared AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 12,729,055
45. 0.011 125.211 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=68) (actual time=125.211..125.211 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
46. 125.200 125.200 ↑ 1.0 40 1

CTE Scan on primarysortedusercte_meeting_current cte0 (cost=0.00..0.80 rows=40 width=68) (actual time=125.188..125.200 rows=40 loops=1)

Planning time : 5.293 ms
Execution time : 6,700.265 ms