explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Qel

Settings
# exclusive inclusive rows x rows loops node
1. 5,431.834 5,431.834 ↑ 36.4 1,956 1

CTE Scan on all_stock_element_ids_with_active_properties (cost=32,335.40..33,759.28 rows=71,194 width=8) (actual time=5,320.506..5,431.834 rows=1,956 loops=1)

2.          

CTE start_stock_element_ids

3. 0.040 0.040 ↑ 1.0 1 1

Index Scan using stock_element_pkey on stock_element (cost=0.42..8.44 rows=1 width=16) (actual time=0.024..0.040 rows=1 loops=1)

  • Index Cond: (stock_element_id = ANY ('{4630140}'::integer[]))
4.          

CTE all_children_stock_elements_ids

5. 23.934 5,234.473 ↑ 1.6 1,956 1

Recursive Union (cost=0.00..29,762.08 rows=3,121 width=8) (actual time=0.024..5,234.473 rows=1,956 loops=1)

6. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on start_stock_element_ids (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.022 rows=1 loops=1)

7. 2,615.565 5,210.517 ↓ 2.1 652 3

Hash Join (cost=0.33..2,969.96 rows=312 width=8) (actual time=823.470..1,736.839 rows=652 loops=3)

  • Hash Cond: (stock_element_1.parent_stock_element_id = all_children_stock_elements_ids.stock_element_id)
8. 2,571.564 2,571.564 ↑ 1.0 142,365 3

Seq Scan on stock_element stock_element_1 (cost=0.00..2,432.65 rows=142,365 width=16) (actual time=0.013..857.188 rows=142,365 loops=3)

9. 11.892 23.388 ↓ 65.2 652 3

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=7.794..7.796 rows=652 loops=3)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 85kB
10. 11.496 11.496 ↓ 65.2 652 3

WorkTable Scan on all_children_stock_elements_ids (cost=0.00..0.20 rows=10 width=8) (actual time=0.007..3.832 rows=652 loops=3)

11.          

CTE all_parent_stock_elements_ids

12. 0.023 0.102 ↑ 101.0 1 1

Recursive Union (cost=0.00..847.79 rows=101 width=16) (actual time=0.019..0.102 rows=1 loops=1)

13. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on start_stock_element_ids start_stock_element_ids_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.007..0.018 rows=1 loops=1)

14. 0.020 0.061 ↓ 0.0 0 1

Nested Loop (cost=0.42..84.58 rows=10 width=16) (actual time=0.055..0.061 rows=0 loops=1)

15. 0.026 0.026 ↑ 10.0 1 1

WorkTable Scan on all_parent_stock_elements_ids (cost=0.00..0.20 rows=10 width=8) (actual time=0.015..0.026 rows=1 loops=1)

16. 0.015 0.015 ↓ 0.0 0 1

Index Scan using stock_element_pkey on stock_element stock_element_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.009..0.015 rows=0 loops=1)

  • Index Cond: (stock_element_id = all_parent_stock_elements_ids.parent_stock_element_id)
17.          

CTE all_stock_element_ids

18. 23.258 5,282.459 ↑ 1.6 1,958 1

Append (cost=0.00..80.58 rows=3,223 width=8) (actual time=0.057..5,282.459 rows=1,958 loops=1)

19. 0.073 0.073 ↑ 1.0 1 1

CTE Scan on start_stock_element_ids start_stock_element_ids_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.041..0.073 rows=1 loops=1)

20. 5,259.002 5,259.002 ↑ 1.6 1,956 1

CTE Scan on all_children_stock_elements_ids all_children_stock_elements_ids_1 (cost=0.00..62.42 rows=3,121 width=8) (actual time=0.041..5,259.002 rows=1,956 loops=1)

21. 0.126 0.126 ↑ 101.0 1 1

CTE Scan on all_parent_stock_elements_ids all_parent_stock_elements_ids_1 (cost=0.00..2.02 rows=101 width=8) (actual time=0.032..0.126 rows=1 loops=1)

22.          

CTE all_stock_element_ids_with_active_properties

23. 46.536 5,408.397 ↑ 36.4 1,956 1

Nested Loop (cost=72.94..1,636.52 rows=71,194 width=8) (actual time=5,320.491..5,408.397 rows=1,956 loops=1)

24. 25.248 5,332.521 ↓ 9.8 1,956 1

HashAggregate (cost=72.52..74.52 rows=200 width=8) (actual time=5,320.453..5,332.521 rows=1,956 loops=1)

  • Group Key: all_stock_element_ids.stock_element_id
25. 5,307.273 5,307.273 ↑ 1.6 1,958 1

CTE Scan on all_stock_element_ids (cost=0.00..64.46 rows=3,223 width=8) (actual time=0.073..5,307.273 rows=1,958 loops=1)

26. 29.340 29.340 ↑ 1.0 1 1,956

Index Scan using ni_stock_element_property_el_id_actv_flg on stock_element_property (cost=0.42..7.80 rows=1 width=8) (actual time=0.009..0.015 rows=1 loops=1,956)

  • Index Cond: ((stock_element_id = all_stock_element_ids.stock_element_id) AND (is_active_flg = true))
  • Filter: (is_active_flg AND (NOT is_deleted_flg))
Planning time : 0.380 ms
Execution time : 5,443.607 ms