explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FuTC

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 0.166 ↓ 1.5 6 1

GroupAggregate (cost=113.54..146.89 rows=4 width=100) (actual time=0.152..0.166 rows=6 loops=1)

  • Output: (SubPlan 1), count(*), count(*) FILTER (WHERE ((d.code)::text = 'entering'::text)), count(*) FILTER (WHERE (((d.code)::text = 'entering'::text) AND ((s.code)::text = 'request_sent'::text))), count(*) FILTER (WHERE (((d.code)::text = 'entering'::text) AND ((s.code)::text = 'answer_to_authority'::text))), count(*) FILTER (WHERE (((d.code)::text = 'entering'::text) AND ((s.code)::text = 'answer_to_applicant'::text))), count(*) FILTER (WHERE ((d.code)::text = 'exiting'::text)), count(*) FILTER (WHERE (((d.code)::text = 'exiting'::text) AND ((s.code)::text = 'answer_to_applicant'::text))), count(*) FILTER (WHERE (((d.code)::text = 'exiting'::text) AND ((s.code)::text = 'answer_to_authority'::text))), c.country_id
  • Group Key: c.country_id
  • Group Key: ()
2. 0.009 0.141 ↓ 3.3 10 1

Sort (cost=113.54..113.55 rows=3 width=48) (actual time=0.141..0.141 rows=10 loops=1)

  • Output: c.country_id, d.code, s.code
  • Sort Key: c.country_id
  • Sort Method: quicksort Memory: 25kB
3. 0.009 0.132 ↓ 3.3 10 1

Nested Loop (cost=9.49..113.52 rows=3 width=48) (actual time=0.103..0.132 rows=10 loops=1)

  • Output: c.country_id, d.code, s.code
  • Join Filter: (c.state_id = s.id)
  • Rows Removed by Join Filter: 60
4. 0.011 0.011 ↑ 1.9 7 1

Seq Scan on safekids.state s (cost=0.00..1.13 rows=13 width=16) (actual time=0.010..0.011 rows=7 loops=1)

  • Output: s.code, s.id
5. 0.005 0.112 ↓ 3.3 10 7

Materialize (cost=9.49..111.81 rows=3 width=40) (actual time=0.011..0.016 rows=10 loops=7)

  • Output: c.country_id, c.state_id, d.code
6. 0.002 0.107 ↓ 3.3 10 1

Nested Loop (cost=9.49..111.79 rows=3 width=40) (actual time=0.074..0.107 rows=10 loops=1)

  • Output: c.country_id, c.state_id, d.code
  • Join Filter: (astat.status_id = status.id)
  • Rows Removed by Join Filter: 1
7. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on safekids.status (cost=0.00..1.04 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Output: status.id, status.code, status.name
  • Filter: ((status.code)::text = 'opened'::text)
  • Rows Removed by Filter: 2
8. 0.000 0.098 ↓ 1.1 11 1

Nested Loop (cost=9.49..110.63 rows=10 width=44) (actual time=0.067..0.098 rows=11 loops=1)

  • Output: c.country_id, c.state_id, astat.status_id, d.code
9. 0.012 0.077 ↓ 1.1 11 1

Nested Loop (cost=9.20..27.46 rows=10 width=44) (actual time=0.062..0.077 rows=11 loops=1)

  • Output: c.country_id, c.id, c.state_id, d.code
10. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on safekids.direction d (cost=0.00..1.02 rows=2 width=36) (actual time=0.005..0.005 rows=2 loops=1)

  • Output: d.id, d.code, d.name
11. 0.018 0.060 ↓ 6.0 6 2

Bitmap Heap Scan on safekids."case" c (cost=9.20..13.21 rows=1 width=16) (actual time=0.029..0.030 rows=6 loops=2)

  • Output: c.id, c.category_id, c.create_dt, c.steal_dt, c.state_id, c.direction_id, c.executor_id, c.country_id, c.central_authority_id, c.fabula, c.delete_dt, c.name
  • Recheck Cond: ((c.direction_id = d.id) AND (c.country_id IS NOT NULL))
  • Heap Blocks: exact=4
12. 0.004 0.042 ↓ 0.0 0 2

BitmapAnd (cost=9.20..9.20 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=2)

13. 0.014 0.014 ↓ 1.2 6 2

Bitmap Index Scan on case_direction_ix (cost=0.00..4.45 rows=5 width=0) (actual time=0.007..0.007 rows=6 loops=2)

  • Index Cond: (c.direction_id = d.id)
14. 0.024 0.024 ↓ 1.1 11 2

Bitmap Index Scan on case_country_ix (cost=0.00..4.49 rows=10 width=0) (actual time=0.012..0.012 rows=11 loops=2)

  • Index Cond: (c.country_id IS NOT NULL)
15. 0.022 0.022 ↑ 1.0 1 11

Index Scan using actual_status_case_status_uix on safekids.actual_status astat (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11)

  • Output: astat.id, astat.case_id, astat.status_id, astat.grounds_decision_id, astat.actual_dt, astat.is_actual, astat.reason
  • Index Cond: (astat.case_id = c.id)
  • Filter: astat.is_actual
  • Rows Removed by Filter: 0
16.          

SubPlan (for GroupAggregate)

17. 0.012 0.012 ↑ 1.0 1 6

Index Scan using country_pkey on safekids.country co (cost=0.27..8.29 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=6)

  • Output: co.name
  • Index Cond: (c.country_id = co.id)
Planning time : 1.234 ms
Execution time : 0.331 ms