explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kiCx

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 3,155.495 ↑ 1.0 40 1

Limit (cost=173,239,300.54..173,245,571.93 rows=40 width=76) (actual time=3,154.877..3,155.495 rows=40 loops=1)

  • Buffers: shared hit=239,247, temp read=814 written=836
2.          

CTE filtered_accounts_cte

3. 17.008 206.029 ↑ 2.1 35,940 1

Hash Join (cost=59,298.97..84,990.69 rows=74,039 width=40) (actual time=140.221..206.029 rows=35,940 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t2268_accountdim.sid)
  • Buffers: shared hit=61,958, temp read=190 written=188
4. 49.198 49.198 ↑ 1.4 35,940 1

Seq Scan on t1401_accountfact (cost=0.00..18,028.11 rows=51,628 width=4) (actual time=0.005..49.198 rows=35,940 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 422,961
  • Buffers: shared hit=10,949
5. 9.455 139.823 ↑ 2.1 35,941 1

Hash (cost=57,786.71..57,786.71 rows=74,421 width=40) (actual time=139.823..139.823 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
  • Buffers: shared hit=51,009, temp written=134
6. 130.368 130.368 ↑ 2.1 35,941 1

Seq Scan on t2268_accountdim (cost=0.00..57,786.71 rows=74,421 width=40) (actual time=0.005..130.368 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 438,189
  • Buffers: shared hit=51,009
7. 0.425 3,155.490 ↑ 185.1 40 1

GroupAggregate (cost=173,154,309.85..174,315,143.80 rows=7,404 width=76) (actual time=3,154.876..3,155.490 rows=40 loops=1)

  • Group Key: cte0.c3, cte0.c1
  • Buffers: shared hit=239,247, temp read=814 written=836
8. 196.562 3,155.065 ↑ 67,407.7 1,722 1

Sort (cost=173,154,309.85..173,444,499.83 rows=116,075,991 width=40) (actual time=3,154.858..3,155.065 rows=1,722 loops=1)

  • Sort Key: cte0.c3, cte0.c1
  • Sort Method: external merge Disk: 3,344kB
  • Buffers: shared hit=239,247, temp read=814 written=836
9. 19.250 2,958.503 ↑ 1,124.6 103,217 1

Merge Join (cost=146,337,537.22..148,083,781.94 rows=116,075,991 width=40) (actual time=2,911.878..2,958.503 rows=103,217 loops=1)

  • Merge Cond: (cte0.c1 = t2608_emailactivityaccountfact_copy_sentha.c9638_accountid)
  • Buffers: shared hit=239,247, temp read=418 written=417
10. 15.459 235.244 ↑ 2.1 35,935 1

Sort (cost=9,495.55..9,680.65 rows=74,039 width=36) (actual time=230.573..235.244 rows=35,935 loops=1)

  • Sort Key: cte0.c1
  • Sort Method: quicksort Memory: 3,470kB
  • Buffers: shared hit=61,958, temp read=190 written=188
11. 219.785 219.785 ↑ 2.1 35,940 1

CTE Scan on filtered_accounts_cte cte0 (cost=0.00..1,480.78 rows=74,039 width=36) (actual time=140.224..219.785 rows=35,940 loops=1)

  • Buffers: shared hit=61,958, temp read=190 written=188
12. 11.976 2,704.009 ↑ 18.3 103,217 1

Materialize (cost=146,328,041.66..146,337,510.98 rows=1,893,864 width=8) (actual time=2,681.298..2,704.009 rows=103,217 loops=1)

  • Buffers: shared hit=177,289, temp read=228 written=229
13. 59.733 2,692.033 ↑ 18.3 103,217 1

Sort (cost=146,328,041.66..146,332,776.32 rows=1,893,864 width=8) (actual time=2,681.295..2,692.033 rows=103,217 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: external merge Disk: 1,824kB
  • Buffers: shared hit=177,289, temp read=228 written=229
14. 2,572.204 2,632.300 ↑ 18.3 103,217 1

Index Scan using idx_isinternal_sentha1 on t2608_emailactivityaccountfact_copy_sentha (cost=2,499,429.28..146,104,685.87 rows=1,893,864 width=8) (actual time=60.624..2,632.300 rows=103,217 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = true) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Filter: (c9660_isinternalinitiated AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (NOT (hashed SubPlan 10)) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9)))
  • Rows Removed by Filter: 5,285,817
  • Buffers: shared hit=177,289
15.          

SubPlan (for Index Scan)

16. 4.053 59.741 ↓ 1.1 1,861 1

Unique (cost=0.42..2,499,424.44 rows=1,710 width=4) (actual time=0.398..59.741 rows=1,861 loops=1)

  • Buffers: shared hit=3,704
17. 55.318 55.688 ↓ 1.3 67,811 1

Index Scan using idx_1992_t2273_meetingactivityaccountfact_c9010_accountid_end_s on t2273_meetingactivityaccountfact (cost=0.42..2,499,291.57 rows=53,149 width=4) (actual time=0.398..55.688 rows=67,811 loops=1)

  • Index Cond: ((end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND c8998_isshared AND (c8986_activitydate >= '1561532400000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))
  • Rows Removed by Filter: 30,834
  • Buffers: shared hit=3,704
18.          

SubPlan (for Index Scan)

19. 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)
20. 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))
21. 0.000 0.000 ↓ 0.0 0

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

22. 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 = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
23. 0.110 0.314 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
  • Buffers: shared hit=76
24. 0.118 0.158 ↑ 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.053..0.158 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
25. 0.040 0.040 ↑ 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.039..0.040 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
26. 0.007 0.046 ↓ 1.5 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
27. 0.039 0.039 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.007..0.039 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
  • Buffers: shared hit=6
28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim (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))
29. 0.040 0.056 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=68 width=4) (actual time=0.025..0.056 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
  • Buffers: shared hit=27
30. 0.016 0.016 ↑ 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.016..0.016 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
31. 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)
32. 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))
33. 0.000 0.000 ↓ 0.0 0

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

34. 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 = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
35. 0.104 0.300 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
  • Buffers: shared hit=76
36. 0.116 0.152 ↑ 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.050..0.152 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
37. 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))
  • Buffers: shared hit=8
38. 0.007 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
  • Buffers: shared hit=6
39. 0.037 0.037 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.005..0.037 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
  • Buffers: shared hit=6
40. 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 = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
41. 0.037 0.055 ↑ 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.026..0.055 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
  • Buffers: shared hit=27
42. 0.018 0.018 ↑ 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.018..0.018 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
Planning time : 3.259 ms
Execution time : 3,158.287 ms