explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JrNK

Settings
# exclusive inclusive rows x rows loops node
1. 373.104 3,744.677 ↓ 4.8 5,838 1

GroupAggregate (cost=144,118,301.15..144,146,723.26 rows=1,208 width=12) (actual time=3,176.932..3,744.677 rows=5,838 loops=1)

  • Group Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
2. 661.950 3,371.573 ↑ 1.9 2,047,388 1

Sort (cost=144,118,301.15..144,127,771.16 rows=3,788,004 width=8) (actual time=3,176.829..3,371.573 rows=2,047,388 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: external merge Disk: 36,112kB
3. 2,709.303 2,709.623 ↑ 1.9 2,047,388 1

Index Scan using idx_isinternal_sentha1 on t2608_emailactivityaccountfact_copy_sentha (cost=0.56..143,600,825.77 rows=3,788,004 width=8) (actual time=0.351..2,709.623 rows=2,047,388 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = true) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::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,669
4.          

SubPlan (for Index Scan)

5. 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)
6. 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))
7. 0.000 0.000 ↓ 0.0 0

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

8. 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))
9. 0.087 0.269 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
10. 0.110 0.138 ↑ 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.041..0.138 rows=359 loops=1)

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

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
12. 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
13. 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.003..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
14. 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))
15. 0.038 0.051 ↑ 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.021..0.051 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
16. 0.013 0.013 ↑ 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.013..0.013 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
Planning time : 1.247 ms
Execution time : 3,752.646 ms