explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xLlV

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 0.266 ↓ 1.5 6 1

Subquery Scan on data (cost=9.49..147.06 rows=4 width=64) (actual time=0.237..0.266 rows=6 loops=1)

  • Output: data.country_name, row_to_json(data.*)
2. 0.016 0.246 ↓ 1.5 6 1

MixedAggregate (cost=9.49..147.01 rows=4 width=100) (actual time=0.230..0.246 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
  • Hash Key: c.country_id
  • Group Key: ()
3. 0.004 0.218 ↓ 3.3 10 1

Nested Loop (cost=9.49..113.52 rows=3 width=48) (actual time=0.175..0.218 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.012 0.203 ↓ 3.3 10 7

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

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

Nested Loop (cost=9.49..111.79 rows=3 width=40) (actual time=0.128..0.191 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.009 0.009 ↑ 1.0 1 1

Seq Scan on safekids.status (cost=0.00..1.04 rows=1 width=4) (actual time=0.008..0.009 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.180 ↓ 1.1 11 1

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

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

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

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

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

  • Output: d.id, d.code, d.name
11. 0.008 0.100 ↓ 6.0 6 2

Bitmap Heap Scan on safekids."case" c (cost=9.20..13.21 rows=1 width=16) (actual time=0.049..0.050 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.092 ↓ 0.0 0 2

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

13. 0.040 0.040 ↓ 1.2 6 2

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

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

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

  • Index Cond: (c.country_id IS NOT NULL)
15. 0.055 0.055 ↑ 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.005..0.005 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 MixedAggregate)

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.298 ms
Execution time : 0.429 ms