explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SueN

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,568.026 ↑ 1.0 40 1

Limit (cost=145,872,929.27..145,872,929.37 rows=40 width=40) (actual time=3,568.021..3,568.026 rows=40 loops=1)

2. 1.710 3,568.022 ↑ 41.6 40 1

Sort (cost=145,872,929.27..145,872,933.43 rows=1,666 width=40) (actual time=3,568.020..3,568.022 rows=40 loops=1)

  • Sort Key: t2268_accountdim.c8860_name
  • Sort Method: top-N heapsort Memory: 30kB
3. 1.070 3,566.312 ↓ 2.4 4,039 1

Nested Loop (cost=145,839,237.16..145,872,876.61 rows=1,666 width=40) (actual time=3,223.689..3,566.312 rows=4,039 loops=1)

4. 1.310 3,549.086 ↓ 3.5 4,039 1

Merge Anti Join (cost=145,839,236.74..145,867,435.20 rows=1,161 width=8) (actual time=3,223.662..3,549.086 rows=4,039 loops=1)

  • Merge Cond: (t1401_accountfact.sid = t2273_meetingactivityaccountfact.c9010_accountid)
5. 5.222 3,448.427 ↓ 4.9 5,838 1

Nested Loop (cost=144,107,974.85..144,135,882.79 rows=1,201 width=8) (actual time=3,133.636..3,448.427 rows=5,838 loops=1)

6. 115.921 3,431.529 ↓ 4.8 5,838 1

Unique (cost=144,107,974.43..144,126,913.07 rows=1,208 width=4) (actual time=3,133.599..3,431.529 rows=5,838 loops=1)

7. 623.188 3,315.608 ↑ 1.9 2,047,385 1

Sort (cost=144,107,974.43..144,117,443.75 rows=3,787,729 width=4) (actual time=3,133.598..3,315.608 rows=2,047,385 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: external merge Disk: 28,144kB
8. 2,692.080 2,692.420 ↑ 1.9 2,047,385 1

Index Scan using idx_isinternal_sentha1 on t2608_emailactivityaccountfact_copy_sentha (cost=0.56..143,590,538.08 rows=3,787,729 width=4) (actual time=0.368..2,692.420 rows=2,047,385 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 ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 3,341,649
9.          

SubPlan (for Index Scan)

10. 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)
11. 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))
12. 0.000 0.000 ↓ 0.0 0

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

13. 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 = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
14. 0.091 0.285 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
15. 0.113 0.151 ↑ 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.051..0.151 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
16. 0.038 0.038 ↑ 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.038..0.038 rows=359 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.035 0.035 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.003..0.035 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
19. 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 = 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))
20. 0.038 0.055 ↑ 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.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
21. 0.017 0.017 ↑ 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.017..0.017 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
22. 11.676 11.676 ↑ 1.0 1 5,838

Index Scan using idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null on t1401_accountfact (cost=0.42..7.41 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,838)

  • Index Cond: ((sid = t2608_emailactivityaccountfact_copy_sentha.c9638_accountid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: (NOT deleted)
23. 3.920 99.349 ↓ 1.1 1,861 1

Unique (cost=1,731,261.89..1,731,527.63 rows=1,710 width=4) (actual time=90.022..99.349 rows=1,861 loops=1)

24. 37.731 95.429 ↓ 1.3 67,710 1

Sort (cost=1,731,261.89..1,731,394.76 rows=53,149 width=4) (actual time=90.021..95.429 rows=67,710 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9010_accountid
  • Sort Method: external merge Disk: 936kB
25. 57.339 57.698 ↓ 1.3 67,811 1

Index Scan using "idx_activitydate_issharedp_del_P_sentha" on t2273_meetingactivityaccountfact (cost=0.42..1,727,090.29 rows=53,149 width=4) (actual time=0.385..57.698 rows=67,811 loops=1)

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

SubPlan (for Index Scan)

27. 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)
28. 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))
29. 0.000 0.000 ↓ 0.0 0

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

30. 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))
31. 0.094 0.296 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
32. 0.114 0.154 ↑ 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.055..0.154 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
33. 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.040..0.040 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
34. 0.009 0.048 ↓ 1.5 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.039 0.039 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.006..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
36. 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))
37. 0.040 0.063 ↑ 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.034..0.063 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
38. 0.023 0.023 ↑ 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.023..0.023 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
39. 16.156 16.156 ↑ 2.0 1 4,039

Index Scan using t2268_accountdim_sidendstampunique on t2268_accountdim (cost=0.42..4.67 rows=2 width=40) (actual time=0.003..0.004 rows=1 loops=4,039)

  • Index Cond: ((sid = t1401_accountfact.sid) AND (end_stamp > '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint))
  • Rows Removed by Filter: 1
Planning time : 4.064 ms
Execution time : 3,573.738 ms