explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y5lo

Settings
# exclusive inclusive rows x rows loops node
1. 0.193 1,587.567 ↑ 1.3 229 1

Sort (cost=286,844.75..286,845.52 rows=309 width=77) (actual time=1,587.557..1,587.567 rows=229 loops=1)

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

CTE activity_id_cte

3. 0.003 2.375 ↑ 1.0 20 1

Limit (cost=45,058.79..45,059.19 rows=20 width=16) (actual time=2.361..2.375 rows=20 loops=1)

4. 0.012 2.372 ↑ 50.6 20 1

GroupAggregate (cost=45,058.79..45,079.28 rows=1,013 width=16) (actual time=2.360..2.372 rows=20 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7794_activitydate, t1662_meetingactivityaccountfact.c7801_activitychecksum
5. 0.498 2.360 ↑ 30.5 34 1

Sort (cost=45,058.79..45,061.38 rows=1,036 width=16) (actual time=2.357..2.360 rows=34 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact.c7794_activitydate DESC NULLS LAST, t1662_meetingactivityaccountfact.c7801_activitychecksum
  • Sort Method: quicksort Memory: 115kB
6. 1.190 1.862 ↓ 1.4 1,408 1

Bitmap Heap Scan on t1662_meetingactivityaccountfact (cost=38.16..45,006.90 rows=1,036 width=16) (actual time=0.807..1.862 rows=1,408 loops=1)

  • Recheck Cond: ((c7809_accountid = 123) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 392
  • Heap Blocks: exact=60
7. 0.099 0.099 ↓ 1.0 1,800 1

Bitmap Index Scan on t1662_meetingactivityaccountfact_917_ptimestamp (cost=0.00..37.90 rows=1,798 width=0) (actual time=0.099..0.099 rows=1,800 loops=1)

  • Index Cond: (c7809_accountid = 123)
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..15.35 rows=1 width=0) (never executed)

  • Join Filter: (t786_userrolehierarchy.c4812_user_role_id = t859_userroledim.sid)
10. 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 = t1662_meetingactivityaccountfact.c7807_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim (cost=0.00..6.82 rows=18 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.138 0.474 ↓ 2.3 354 1

Hash Join (cost=23.05..95.54 rows=152 width=4) (actual time=0.154..0.474 rows=354 loops=1)

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
13. 0.208 0.263 ↑ 1.0 354 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_1 (cost=16.00..87.45 rows=363 width=8) (actual time=0.073..0.263 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
14. 0.055 0.055 ↑ 1.0 354 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.91 rows=363 width=0) (actual time=0.055..0.055 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
15. 0.010 0.073 ↓ 2.4 44 1

Hash (cost=6.82..6.82 rows=18 width=4) (actual time=0.073..0.073 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
16. 0.063 0.063 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..6.82 rows=18 width=4) (actual time=0.008..0.063 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
17. 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 = t1662_meetingactivityaccountfact.c7807_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
18. 0.071 0.099 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=67 width=4) (actual time=0.041..0.099 rows=29 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=24
19. 0.028 0.028 ↑ 1.8 38 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.028..0.028 rows=38 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
20. 0.399 1,587.374 ↑ 1.3 229 1

Nested Loop (cost=1.50..241,772.78 rows=309 width=77) (actual time=5.323..1,587.374 rows=229 loops=1)

21. 0.297 1,586.059 ↑ 1.3 229 1

Nested Loop (cost=1.08..241,629.95 rows=309 width=24) (actual time=5.306..1,586.059 rows=229 loops=1)

22. 207.309 1,584.388 ↑ 1.4 229 1

Hash Semi Join (cost=0.65..241,433.70 rows=330 width=12) (actual time=5.281..1,584.388 rows=229 loops=1)

  • Hash Cond: (t1666_meetingactivityparticipantsfact.c7822_activityid = cte0.c1)
23. 1,374.687 1,374.687 ↑ 1.0 2,190,018 1

Seq Scan on t1666_meetingactivityparticipantsfact (cost=0.00..235,655.84 rows=2,199,444 width=8) (actual time=0.009..1,374.687 rows=2,190,018 loops=1)

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 4,240,185
24. 0.008 2.392 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=4) (actual time=2.392..2.392 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 2.384 2.384 ↑ 1.0 20 1

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

26. 1.374 1.374 ↑ 1.0 1 229

Index Scan using t1527_meetingactivityparticipantsdim_sidendstampunique on t1527_meetingactivityparticipantsdim (cost=0.43..0.59 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=229)

  • Index Cond: ((sid = t1666_meetingactivityparticipantsfact.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
27. 0.916 0.916 ↑ 1.0 1 229

Index Scan using t1523_meetingactivitydim_sidendstampunique on t1523_meetingactivitydim (cost=0.42..0.46 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=229)

  • Index Cond: ((sid = t1666_meetingactivityparticipantsfact.c7822_activityid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
Planning time : 6.486 ms
Execution time : 1,588.012 ms