explain.depesz.com

PostgreSQL's explain analyze made readable

Result: irCY

Settings
# exclusive inclusive rows x rows loops node
1. 0.143 31.134 ↑ 5.5 16 1

Hash Left Join (cost=752.44..9,317.56 rows=88 width=1,653) (actual time=30.190..31.134 rows=16 loops=1)

  • Hash Cond: ((stop_values.stop_id = tickets_crosstab.stop_id) AND (stop_values.client_day = tickets_crosstab.client_day))
2. 0.010 30.825 ↑ 5.5 16 1

Nested Loop Left Join (cost=315.92..8,873.11 rows=88 width=1,106) (actual time=29.970..30.825 rows=16 loops=1)

3. 0.015 30.815 ↑ 5.5 16 1

Nested Loop Left Join (cost=315.77..8,845.64 rows=88 width=1,113) (actual time=29.969..30.815 rows=16 loops=1)

4. 0.010 30.800 ↑ 5.5 16 1

Nested Loop Left Join (cost=315.63..8,818.18 rows=88 width=1,104) (actual time=29.967..30.800 rows=16 loops=1)

5. 0.008 30.758 ↑ 5.5 16 1

Nested Loop Left Join (cost=315.48..8,790.71 rows=88 width=1,095) (actual time=29.957..30.758 rows=16 loops=1)

  • Join Filter: ((targets.client_day = stop_values.client_day) AND (targets.item_id = lookup.production_id))
6. 0.022 2.254 ↑ 5.5 16 1

Hash Left Join (cost=315.48..503.65 rows=88 width=1,097) (actual time=1.460..2.254 rows=16 loops=1)

  • Hash Cond: (stop_values.stop_id = stop_header.stop_id)
7. 0.019 1.884 ↑ 5.5 16 1

Hash Join (cost=266.65..453.61 rows=88 width=1,089) (actual time=1.101..1.884 rows=16 loops=1)

  • Hash Cond: (stops.route_id = routes.id)
8. 0.019 1.825 ↑ 5.5 16 1

Hash Join (cost=246.02..431.77 rows=88 width=1,060) (actual time=1.051..1.825 rows=16 loops=1)

  • Hash Cond: (stop_values.stop_id = stops.id)
9. 0.492 1.196 ↑ 5.5 16 1

Hash Right Join (cost=156.63..341.17 rows=88 width=1,025) (actual time=0.429..1.196 rows=16 loops=1)

  • Hash Cond: (items.id = lookup.well_id)
10. 0.305 0.305 ↑ 1.0 4,270 1

Seq Scan on items (cost=0.00..167.70 rows=4,270 width=175) (actual time=0.001..0.305 rows=4,270 loops=1)

11. 0.015 0.399 ↑ 5.5 16 1

Hash (cost=155.53..155.53 rows=88 width=866) (actual time=0.398..0.399 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.021 0.384 ↑ 5.5 16 1

Hash Join (cost=34.55..155.53 rows=88 width=866) (actual time=0.367..0.384 rows=16 loops=1)

  • Hash Cond: (stop_values.stop_id = lookup.stop_id)
13. 0.034 0.034 ↑ 5.5 16 1

Index Scan using batch_id_daily_stop_values_backing on daily_stop_values_backing stop_values (cost=0.43..120.09 rows=88 width=818) (actual time=0.025..0.034 rows=16 loops=1)

  • Index Cond: (batch_id = '76f72369-e1eb-4454-83fe-3ebabdbff813'::uuid)
14. 0.191 0.329 ↑ 1.0 1,072 1

Hash (cost=20.72..20.72 rows=1,072 width=48) (actual time=0.329..0.329 rows=1,072 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
15. 0.138 0.138 ↑ 1.0 1,072 1

Seq Scan on production_item lookup (cost=0.00..20.72 rows=1,072 width=48) (actual time=0.004..0.138 rows=1,072 loops=1)

16. 0.267 0.610 ↑ 1.0 1,049 1

Hash (cost=76.06..76.06 rows=1,066 width=67) (actual time=0.610..0.610 rows=1,049 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
17. 0.343 0.343 ↑ 1.0 1,049 1

Seq Scan on stops (cost=0.00..76.06 rows=1,066 width=67) (actual time=0.008..0.343 rows=1,049 loops=1)

  • Filter: ((deleted)::text <> 'true'::text)
  • Rows Removed by Filter: 22
18. 0.008 0.040 ↑ 1.0 21 1

Hash (cost=20.37..20.37 rows=21 width=61) (actual time=0.040..0.040 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.018 0.032 ↑ 1.0 21 1

Hash Left Join (cost=17.88..20.37 rows=21 width=61) (actual time=0.023..0.032 rows=21 loops=1)

  • Hash Cond: (routes.organization_unit_id = a.id)
20. 0.006 0.006 ↑ 1.0 21 1

Seq Scan on routes (cost=0.00..2.21 rows=21 width=52) (actual time=0.004..0.006 rows=21 loops=1)

21. 0.004 0.008 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=41) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.004 0.004 ↑ 87.5 4 1

Seq Scan on stg_organization_units a (cost=0.00..13.50 rows=350 width=41) (actual time=0.002..0.004 rows=4 loops=1)

23. 0.162 0.348 ↓ 1.0 1,071 1

Hash (cost=35.59..35.59 rows=1,059 width=24) (actual time=0.348..0.348 rows=1,071 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 67kB
24. 0.186 0.186 ↓ 1.0 1,071 1

Seq Scan on stop_headers stop_header (cost=0.00..35.59 rows=1,059 width=24) (actual time=0.002..0.186 rows=1,071 loops=1)

25. 0.004 28.496 ↓ 0.0 0 16

Materialize (cost=0.00..8,285.52 rows=1 width=34) (actual time=1.781..1.781 rows=0 loops=16)

26. 28.492 28.492 ↓ 0.0 0 1

Seq Scan on targets (cost=0.00..8,285.51 rows=1 width=34) (actual time=28.491..28.492 rows=0 loops=1)

  • Filter: (target_type_code = 1)
  • Rows Removed by Filter: 324525
27. 0.032 0.032 ↑ 1.0 1 16

Index Scan using organization_units_pkey on stg_organization_units b (cost=0.15..0.30 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=16)

  • Index Cond: (a.parent_unit_id = id)
28. 0.000 0.000 ↓ 0.0 0 16

Index Scan using organization_units_pkey on stg_organization_units c (cost=0.15..0.30 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=16)

  • Index Cond: (b.parent_unit_id = id)
29. 0.000 0.000 ↓ 0.0 0 16

Index Scan using organization_units_pkey on stg_organization_units d (cost=0.15..0.30 rows=1 width=25) (actual time=0.000..0.000 rows=0 loops=16)

  • Index Cond: (c.parent_unit_id = id)
30. 0.000 0.166 ↓ 0.0 0 1

Hash (cost=435.75..435.75 rows=51 width=84) (actual time=0.166..0.166 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
31. 0.001 0.166 ↓ 0.0 0 1

GroupAggregate (cost=430.91..435.24 rows=51 width=84) (actual time=0.166..0.166 rows=0 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day
32. 0.001 0.165 ↓ 0.0 0 1

GroupAggregate (cost=430.91..433.46 rows=51 width=116) (actual time=0.165..0.165 rows=0 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
33. 0.011 0.164 ↓ 0.0 0 1

Sort (cost=430.91..431.03 rows=51 width=68) (actual time=0.164..0.164 rows=0 loops=1)

  • Sort Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
  • Sort Method: quicksort Memory: 25kB
34. 0.000 0.153 ↓ 0.0 0 1

Nested Loop (cost=0.42..429.46 rows=51 width=68) (actual time=0.153..0.153 rows=0 loops=1)

35. 0.005 0.005 ↑ 1.0 51 1

Seq Scan on stg_forms forms (cost=0.00..2.51 rows=51 width=20) (actual time=0.003..0.005 rows=51 loops=1)

36. 0.153 0.153 ↓ 0.0 0 51

Index Scan using form_id_tickets_crosstab on tickets_crosstab (cost=0.42..8.36 rows=1 width=48) (actual time=0.003..0.003 rows=0 loops=51)

  • Index Cond: (form_id = forms.id)
Planning time : 3.098 ms