explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xwdI

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 2.009 ↑ 1.6 233 1

Sort (cost=40,573.80..40,574.74 rows=374 width=77) (actual time=1.999..2.009 rows=233 loops=1)

  • Sort Key: t2264_meetingactivitydim.c8792_activitydate DESC NULLS LAST, (CASE WHEN (t2264_meetingactivitydim.c8772_activitysubject IS NULL) THEN ''::text ELSE t2264_meetingactivitydim.c8772_activitysubject END)
  • Sort Method: quicksort Memory: 53kB
2.          

CTE activity_id_cte

3. 0.003 0.974 ↑ 1.0 20 1

Limit (cost=40,131.20..40,131.60 rows=20 width=16) (actual time=0.965..0.974 rows=20 loops=1)

4. 0.007 0.971 ↑ 1.4 20 1

GroupAggregate (cost=40,131.20..40,131.74 rows=27 width=16) (actual time=0.964..0.971 rows=20 loops=1)

  • Group Key: t2273_meetingactivityaccountfact.c8986_activitydate, t2273_meetingactivityaccountfact.c8992_activitychecksum
5. 0.014 0.964 ↓ 1.0 28 1

Sort (cost=40,131.20..40,131.27 rows=27 width=16) (actual time=0.962..0.964 rows=28 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c8986_activitydate DESC NULLS LAST, t2273_meetingactivityaccountfact.c8992_activitychecksum
  • Sort Method: quicksort Memory: 26kB
6. 0.546 0.950 ↓ 1.0 28 1

Bitmap Heap Scan on t2273_meetingactivityaccountfact (cost=40.00..40,130.56 rows=27 width=16) (actual time=0.448..0.950 rows=28 loops=1)

  • Recheck Cond: ((c9010_accountid = 123) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND c8998_isshared AND (c8986_activitydate >= '1592550000000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 1,822
  • Heap Blocks: exact=141
7. 0.074 0.074 ↓ 1.2 1,850 1

Bitmap Index Scan on t2273_meetingactivityaccountfact_917_ptimestamp (cost=0.00..39.99 rows=1,543 width=0) (actual time=0.074..0.074 rows=1,850 loops=1)

  • Index Cond: (c9010_accountid = 123)
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 = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
13. 0.088 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.142 ↑ 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.044..0.142 rows=359 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.037 0.037 ↓ 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.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
18. 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))
19. 0.037 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
20. 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))
21. 0.168 1.929 ↑ 1.6 233 1

Nested Loop (cost=1.73..426.22 rows=374 width=77) (actual time=1.028..1.929 rows=233 loops=1)

22. 0.049 1.295 ↑ 1.6 233 1

Nested Loop (cost=1.30..203.52 rows=374 width=44) (actual time=1.016..1.295 rows=233 loops=1)

23. 0.019 1.066 ↑ 1.0 20 1

Nested Loop (cost=0.87..169.50 rows=20 width=44) (actual time=0.995..1.066 rows=20 loops=1)

24. 0.007 0.987 ↑ 1.0 20 1

HashAggregate (cost=0.45..0.65 rows=20 width=4) (actual time=0.984..0.987 rows=20 loops=1)

  • Group Key: cte0.c1
25. 0.980 0.980 ↑ 1.0 20 1

CTE Scan on activity_id_cte cte0 (cost=0.00..0.40 rows=20 width=4) (actual time=0.967..0.980 rows=20 loops=1)

26. 0.060 0.060 ↑ 1.0 1 20

Index Scan using idx_1809_t2264_meetingactivitydim_sid_end_stamp_start_stamp_nul on t2264_meetingactivitydim (cost=0.42..8.44 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: ((sid = cte0.c1) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
27. 0.180 0.180 ↑ 1.6 12 20

Index Scan using idx_activityid_sentha on t2286_meetingactivityparticipantsfact (cost=0.43..1.51 rows=19 width=8) (actual time=0.005..0.009 rows=12 loops=20)

  • Index Cond: ((c9160_activityid = t2264_meetingactivitydim.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
28. 0.466 0.466 ↑ 1.0 1 233

Index Scan using t2256_meetingactivityparticipantsdim_sidendstampunique on t2256_meetingactivityparticipantsdim (cost=0.43..0.60 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=233)

  • Index Cond: ((sid = t2286_meetingactivityparticipantsfact.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
Planning time : 2.552 ms
Execution time : 2.207 ms