explain.depesz.com

PostgreSQL's explain analyze made readable

Result: niGu

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 565.809 ↓ 0.0 0 1

Insert on daily_well_values (cost=68,328.83..68,328.90 rows=1 width=2,148) (actual time=565.809..565.809 rows=0 loops=1)

2.          

CTE newvalues

3. 0.001 565.802 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,829.91..68,320.33 rows=1 width=1,179) (actual time=565.802..565.802 rows=0 loops=1)

4. 0.001 565.801 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,829.76..68,319.96 rows=1 width=1,183) (actual time=565.801..565.801 rows=0 loops=1)

5. 0.001 565.800 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,829.62..68,319.67 rows=1 width=1,171) (actual time=565.800..565.800 rows=0 loops=1)

6. 0.001 565.799 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,829.47..68,319.38 rows=1 width=1,159) (actual time=565.799..565.799 rows=0 loops=1)

  • Join Filter: ((tickets_crosstab.stop_id = stop_values.stop_id) AND (tickets_crosstab.client_day = stop_values.client_day))
7. 0.001 565.798 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..61,456.62 rows=1 width=1,095) (actual time=565.798..565.798 rows=0 loops=1)

  • Join Filter: (routes.organization_unit_id = a.id)
8. 0.000 565.797 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..61,437.39 rows=1 width=1,083) (actual time=565.797..565.797 rows=0 loops=1)

  • Join Filter: ((targets.client_day = stop_values.client_day) AND (targets.item_id = lookup.production_id))
9. 0.001 565.797 ↓ 0.0 0 1

Nested Loop (cost=0.00..53,591.75 rows=1 width=1,085) (actual time=565.797..565.797 rows=0 loops=1)

  • Join Filter: (stops.route_id = routes.id)
10. 0.001 565.796 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..53,588.81 rows=1 width=1,065) (actual time=565.796..565.796 rows=0 loops=1)

  • Join Filter: (stop_values.stop_id = stop_header.stop_id)
11. 0.000 565.795 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..53,539.71 rows=1 width=1,057) (actual time=565.795..565.795 rows=0 loops=1)

  • Join Filter: (lookup.well_id = items.id)
12. 0.002 565.795 ↓ 0.0 0 1

Nested Loop (cost=0.00..53,318.64 rows=1 width=898) (actual time=565.795..565.795 rows=0 loops=1)

  • Join Filter: (stop_values.stop_id = lookup.stop_id)
13. 0.002 565.793 ↓ 0.0 0 1

Nested Loop (cost=0.00..53,284.51 rows=1 width=882) (actual time=565.793..565.793 rows=0 loops=1)

  • Join Filter: (stop_values.stop_id = stops.id)
14. 565.791 565.791 ↓ 0.0 0 1

Seq Scan on daily_stop_values_backing stop_values (cost=0.00..53,219.12 rows=1 width=815) (actual time=565.791..565.791 rows=0 loops=1)

  • Filter: (batch_id = 'c163a943-dd58-4831-9821-8dbdf34112b0'::uuid)
  • Rows Removed by Filter: 1443477
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on stops (cost=0.00..52.06 rows=1,066 width=67) (never executed)

  • Filter: ((deleted)::text <> 'true'::text)
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on production_item lookup (cost=0.00..20.72 rows=1,072 width=48) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on items (cost=0.00..167.70 rows=4,270 width=175) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on stop_headers stop_header (cost=0.00..35.71 rows=1,071 width=24) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on routes (cost=0.00..2.42 rows=42 width=52) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on targets (cost=0.00..7,845.62 rows=1 width=34) (never executed)

  • Filter: (target_type_code = 1)
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on stg_organization_units a (cost=0.00..14.10 rows=410 width=44) (never executed)

22. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=5,829.47..6,827.83 rows=1,397 width=84) (never executed)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day
23. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=5,829.47..6,527.62 rows=13,963 width=36) (never executed)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
24. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,829.47..5,864.38 rows=13,963 width=36) (never executed)

  • Sort Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=612.17..4,868.16 rows=13,963 width=36) (never executed)

  • Hash Cond: (tickets_crosstab.form_id = forms.id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on tickets_crosstab (cost=0.00..2,920.20 rows=100,120 width=48) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=437.63..437.63 rows=13,963 width=20) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on stg_forms forms (cost=0.00..437.63 rows=13,963 width=20) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_units_pkey on stg_organization_units b (cost=0.15..0.28 rows=1 width=44) (never executed)

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

Index Scan using organization_units_pkey on stg_organization_units c (cost=0.15..0.28 rows=1 width=44) (never executed)

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

Index Scan using organization_units_pkey on stg_organization_units d (cost=0.15..0.28 rows=1 width=28) (never executed)

  • Index Cond: (c.parent_unit_id = id)
32.          

CTE upsert

33. 0.002 0.004 ↓ 0.0 0 1

Update on daily_well_values daily_well_values_1 (cost=0.43..8.49 rows=1 width=3,836) (actual time=0.004..0.004 rows=0 loops=1)

34. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.43..8.49 rows=1 width=3,836) (actual time=0.002..0.002 rows=0 loops=1)

35. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on newvalues nv_1 (cost=0.00..0.02 rows=1 width=3,552) (actual time=0.001..0.001 rows=0 loops=1)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using stop_id_client_day_daily_well_values on daily_well_values daily_well_values_1 (cost=0.43..8.46 rows=1 width=320) (never executed)

  • Index Cond: ((stop_id = nv_1.stop_id) AND (client_day = nv_1.client_day))
  • Filter: (COALESCE((nv_1.item_id)::text, '1'::text) = COALESCE((item_id)::text, '1'::text))
37. 0.000 565.805 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.00..0.07 rows=1 width=2,148) (actual time=565.805..565.805 rows=0 loops=1)

  • Join Filter: ((up.stop_id = nv.stop_id) AND (up.client_day = nv.client_day) AND (COALESCE((up.item_id)::text, '1'::text) = COALESCE((nv.item_id)::text, '1'::text)))
38. 565.805 565.805 ↓ 0.0 0 1

CTE Scan on newvalues nv (cost=0.00..0.02 rows=1 width=2,148) (actual time=565.805..565.805 rows=0 loops=1)

39. 0.000 0.000 ↓ 0.0 0

CTE Scan on upsert up (cost=0.00..0.02 rows=1 width=36) (never executed)

Planning time : 11.141 ms