explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7rXA

Settings
# exclusive inclusive rows x rows loops node
1. 0.235 7.815 ↓ 142.0 284 1

GroupAggregate (cost=119.77..119.89 rows=2 width=241) (actual time=7.573..7.815 rows=284 loops=1)

  • Group Key: r.away_team, (CASE WHEN ((rt.display_name)::text <> 'Purchase'::text) THEN d.name ELSE NULL::character varying END), creatorname.employee_name, rs.display_name, r.home_team, e.event_date, e.event_unique_id, st.name, r.request_number, r.ticket_count, requestername.employee_name, (CASE WHEN ((rt.display_name)::text <> 'Purchase'::text) THEN r.gl_code ELSE NULL::character varying END)
2. 1.508 7.580 ↓ 227.5 455 1

Sort (cost=119.77..119.77 rows=2 width=233) (actual time=7.564..7.580 rows=455 loops=1)

  • Sort Key: r.away_team, (CASE WHEN ((rt.display_name)::text <> 'Purchase'::text) THEN d.name ELSE NULL::character varying END), creatorname.employee_name, rs.display_name, r.home_team, e.event_date, e.event_unique_id, st.name, r.request_number, r.ticket_count, requestername.employee_name, (CASE WHEN ((rt.display_name)::text <> 'Purchase'::text) THEN r.gl_code ELSE NULL::character varying END)
  • Sort Method: quicksort Memory: 145kB
3. 0.076 6.072 ↓ 227.5 455 1

Nested Loop Left Join (cost=96.94..119.76 rows=2 width=233) (actual time=4.157..6.072 rows=455 loops=1)

4. 0.293 5.541 ↓ 227.5 455 1

Nested Loop Left Join (cost=96.67..118.98 rows=2 width=425) (actual time=4.131..5.541 rows=455 loops=1)

5. 0.013 4.793 ↓ 227.5 455 1

Nested Loop (cost=96.38..111.01 rows=2 width=433) (actual time=4.123..4.793 rows=455 loops=1)

6. 0.128 4.325 ↓ 227.5 455 1

Hash Right Join (cost=96.24..110.55 rows=2 width=435) (actual time=4.115..4.325 rows=455 loops=1)

  • Hash Cond: (rtm.request_id = r.id)
7. 0.091 0.091 ↑ 1.1 277 1

Seq Scan on request_ticket_map rtm (cost=0.00..13.12 rows=312 width=32) (actual time=0.006..0.091 rows=277 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 110
8. 0.082 4.106 ↓ 142.0 284 1

Hash (cost=96.21..96.21 rows=2 width=435) (actual time=4.106..4.106 rows=284 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
9. 0.081 4.024 ↓ 142.0 284 1

Hash Right Join (cost=86.03..96.21 rows=2 width=435) (actual time=3.901..4.024 rows=284 loops=1)

  • Hash Cond: (uam.account_id = r.account_id)
10. 0.052 0.052 ↑ 1.0 375 1

Seq Scan on department_account_map uam (cost=0.00..8.76 rows=375 width=32) (actual time=0.006..0.052 rows=375 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 1
11. 0.106 3.891 ↓ 142.0 284 1

Hash (cost=86.00..86.00 rows=2 width=435) (actual time=3.891..3.891 rows=284 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
12. 0.067 3.785 ↓ 142.0 284 1

Nested Loop (cost=60.68..86.00 rows=2 width=435) (actual time=2.150..3.785 rows=284 loops=1)

13. 0.078 3.434 ↓ 142.0 284 1

Nested Loop (cost=60.53..85.36 rows=2 width=451) (actual time=2.144..3.434 rows=284 loops=1)

14. 0.078 3.072 ↓ 142.0 284 1

Nested Loop (cost=60.26..84.10 rows=2 width=453) (actual time=2.136..3.072 rows=284 loops=1)

15. 0.000 2.710 ↓ 142.0 284 1

Nested Loop (cost=59.98..82.83 rows=2 width=455) (actual time=2.124..2.710 rows=284 loops=1)

16. 0.146 2.427 ↓ 71.0 284 1

Nested Loop (cost=59.85..82.09 rows=4 width=253) (actual time=2.110..2.427 rows=284 loops=1)

  • Join Filter: (s.type = st.id)
  • Rows Removed by Join Filter: 568
17. 0.099 2.281 ↓ 35.5 284 1

Hash Join (cost=59.85..80.82 rows=8 width=237) (actual time=2.088..2.281 rows=284 loops=1)

  • Hash Cond: (r.event_id = e.id)
18. 0.106 0.106 ↓ 1.0 284 1

Seq Scan on request r (cost=0.00..19.83 rows=283 width=206) (actual time=0.009..0.106 rows=284 loops=1)

  • Filter: (is_active AND is_active)
19. 0.196 2.076 ↓ 36.3 1,271 1

Hash (cost=59.41..59.41 rows=35 width=63) (actual time=2.076..2.076 rows=1,271 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 134kB
20. 1.678 1.880 ↓ 36.3 1,271 1

Hash Join (cost=1.10..59.41 rows=35 width=63) (actual time=0.025..1.880 rows=1,271 loops=1)

  • Hash Cond: (e.season_id = s.id)
  • Join Filter: (((e.event_date)::date >= (s.start_date)::date) AND ((e.event_date)::date <= (s.end_date)::date))
21. 0.191 0.191 ↑ 1.0 1,271 1

Seq Scan on event e (cost=0.00..53.71 rows=1,271 width=63) (actual time=0.005..0.191 rows=1,271 loops=1)

22. 0.002 0.011 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=96) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on season s (cost=0.00..1.09 rows=1 width=96) (actual time=0.009..0.009 rows=1 loops=1)

  • Filter: (is_active AND ((start_year)::text = '2019'::text) AND ((end_year)::text = '2020'::text) AND (((type)::character varying)::text = 'e9d61527-def7-46be-9167-bb1644185768'::text))
  • Rows Removed by Filter: 3
24. 0.000 0.000 ↓ 1.5 3 284

Materialize (cost=0.00..1.05 rows=2 width=48) (actual time=0.000..0.000 rows=3 loops=284)

25. 0.019 0.019 ↓ 1.5 3 1

Seq Scan on season_type st (cost=0.00..1.04 rows=2 width=48) (actual time=0.018..0.019 rows=3 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 1
26. 0.284 0.284 ↑ 1.0 1 284

Index Scan using pk_request_type on request_type rt (cost=0.13..0.19 rows=1 width=234) (actual time=0.001..0.001 rows=1 loops=284)

  • Index Cond: (id = r.request_type)
  • Filter: is_active
27. 0.284 0.284 ↑ 1.0 1 284

Index Scan using pk_user_info on user_info creatorname (cost=0.28..0.63 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=284)

  • Index Cond: (id = r.creator)
  • Filter: is_active
28. 0.284 0.284 ↑ 1.0 1 284

Index Scan using pk_user_info on user_info requestername (cost=0.28..0.63 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=284)

  • Index Cond: (id = r.requester)
  • Filter: is_active
29. 0.284 0.284 ↑ 1.0 1 284

Index Scan using pk_request_details on request_details rd (cost=0.15..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=284)

  • Index Cond: (id = r.request_details_id)
  • Filter: is_active
30. 0.455 0.455 ↑ 1.0 1 455

Index Scan using pk_request_status on request_status rs (cost=0.14..0.23 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=455)

  • Index Cond: (id = r.request_status)
  • Filter: (is_active IS TRUE)
31. 0.455 0.455 ↑ 1.0 1 455

Index Scan using pk_ticket on ticket ti (cost=0.29..3.98 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=455)

  • Index Cond: (id = rtm.ticket_id)
  • Filter: is_active
32. 0.455 0.455 ↑ 1.0 1 455

Index Scan using pk_department on department d (cost=0.27..0.39 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=455)

  • Index Cond: (uam.department_id = id)
  • Filter: is_active