explain.depesz.com

PostgreSQL's explain analyze made readable

Result: clSJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=340,178.67..340,187.51 rows=3,538 width=1,268) (actual rows= loops=)

  • Sort Key: results.task_sn, parents.result_value, results.repeat_number
2.          

CTE results

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,547.07..121,424.49 rows=3,538 width=241) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,546.78..119,976.85 rows=3,538 width=169) (actual rows= loops=)

  • Hash Cond: (pd.point_state_id = dpa.point_state_id)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,542.32..119,923.75 rows=3,538 width=152) (actual rows= loops=)

  • Hash Cond: (rp.point_id = pd.point_id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.44..115,774.96 rows=3,121 width=91) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on route_points rp (cost=0.00..8,659.38 rows=734 width=24) (actual rows= loops=)

  • Filter: ((task_stop_time >= '2019-02-26 00:00:00'::timestamp without time zone) AND (task_stop_time <= '2019-02-26 23:59:59'::timestamp without time zone))
8. 0.000 0.000 ↓ 0.0

Index Scan using task_result_task_sn_index on task_result (cost=0.44..145.37 rows=56 width=75) (actual rows= loops=)

  • Index Cond: (task_sn = rp.task_sn)
  • Filter: (form_element_id = 60)
9. 0.000 0.000 ↓ 0.0

Hash (cost=2,706.91..2,706.91 rows=33,118 width=77) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on point_details pd (cost=0.00..2,706.91 rows=33,118 width=77) (actual rows= loops=)

  • Filter: (point_edt = '9999-12-31 00:00:00'::timestamp without time zone)
11. 0.000 0.000 ↓ 0.0

Hash (cost=3.09..3.09 rows=109 width=33) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on dict_point_states dpa (cost=0.00..3.09 rows=109 width=33) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using dict_customer_l_details_pkey on dict_customer_l_details dcld (cost=0.29..0.37 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (customer_id = pd.customer_id)
14.          

CTE second_results

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=80.04..31,597.29 rows=397,488 width=107) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=79.61..81.61 rows=200 width=8) (actual rows= loops=)

  • Group Key: results_1.task_sn
17. 0.000 0.000 ↓ 0.0

CTE Scan on results results_1 (cost=0.00..70.76 rows=3,538 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using task_result_task_sn_index on task_result task_result_1 (cost=0.44..157.04 rows=54 width=107) (actual rows= loops=)

  • Index Cond: (task_sn = results_1.task_sn)
  • Filter: (form_element_id = 61)
19.          

CTE parents

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.44..117,854.02 rows=6 width=51) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on route_points rp_1 (cost=0.00..8,659.38 rows=734 width=8) (actual rows= loops=)

  • Filter: ((task_stop_time >= '2019-02-26 00:00:00'::timestamp without time zone) AND (task_stop_time <= '2019-02-26 23:59:59'::timestamp without time zone))
22. 0.000 0.000 ↓ 0.0

Index Scan using task_result_task_sn_index on task_result task_result_2 (cost=0.44..148.76 rows=1 width=51) (actual rows= loops=)

  • Index Cond: (task_sn = rp_1.task_sn)
  • Filter: ((result_value = '030. ФрутоНяня (вода)'::text) AND (form_element_id = 62))
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=62,077.25..67,134.32 rows=3,538 width=1,268) (actual rows= loops=)

  • Hash Cond: ((results.task_sn = parents.task_sn) AND (results.parent_container_repeat_number = (parents.container_repeat_number)::text))
24. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=62,077.04..67,098.72 rows=3,538 width=1,268) (actual rows= loops=)

  • Merge Cond: ((sr.container_repeat_number = results.container_repeat_number) AND (sr.container_id = results.container_id) AND (sr.repeat_number = results.repeat_number) AND (sr.task_sn = results.task_sn))
25. 0.000 0.000 ↓ 0.0

Sort (cost=59,862.24..60,855.96 rows=397,488 width=64) (actual rows= loops=)

  • Sort Key: sr.container_repeat_number, sr.container_id, sr.repeat_number, sr.task_sn
26. 0.000 0.000 ↓ 0.0

CTE Scan on second_results sr (cost=0.00..7,949.76 rows=397,488 width=64) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Materialize (cost=2,214.80..2,232.49 rows=3,538 width=1,252) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Sort (cost=2,214.80..2,223.65 rows=3,538 width=1,252) (actual rows= loops=)

  • Sort Key: results.container_repeat_number, results.container_id, results.repeat_number, results.task_sn
29. 0.000 0.000 ↓ 0.0

CTE Scan on results (cost=0.00..70.76 rows=3,538 width=1,252) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=0.12..0.12 rows=6 width=48) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

CTE Scan on parents (cost=0.00..0.12 rows=6 width=48) (actual rows= loops=)