explain.depesz.com

PostgreSQL's explain analyze made readable

Result: igpc : updated

Settings
# exclusive inclusive rows x rows loops node
1. 1,659.629 1,659.629 ↓ 2.0 142,365 1

CTE Scan on all_stock_element_ids_with_active_properties (cost=1,493,792.00..1,495,215.66 rows=71,183 width=8) (actual time=1,116.473..1,659.629 rows=142,365 loops=1)

2.          

CTE start_stock_element_ids

3. 21.869 21.869 ↑ 1.0 142,365 1

Seq Scan on stock_element (cost=0.00..2,432.65 rows=142,365 width=16) (actual time=0.011..21.869 rows=142,365 loops=1)

4.          

CTE all_children_stock_elements_ids

5. 17.975 67.630 ↑ 1.0 284,730 1

Append (cost=0.00..6,703.60 rows=284,730 width=8) (actual time=0.011..67.630 rows=284,730 loops=1)

6. 27.591 27.591 ↑ 1.0 142,365 1

CTE Scan on start_stock_element_ids (cost=0.00..2,847.30 rows=142,365 width=8) (actual time=0.010..27.591 rows=142,365 loops=1)

7. 22.064 22.064 ↑ 1.0 142,365 1

Seq Scan on stock_element stock_element_1 (cost=0.00..2,432.65 rows=142,365 width=8) (actual time=0.018..22.064 rows=142,365 loops=1)

8.          

CTE all_parent_stock_elements_ids

9. 58.808 316.261 ↑ 41.9 343,281 1

Recursive Union (cost=0.00..779,808.65 rows=14,378,865 width=16) (actual time=0.008..316.261 rows=343,281 loops=1)

10. 18.062 18.062 ↑ 1.0 142,365 1

CTE Scan on start_stock_element_ids start_stock_element_ids_1 (cost=0.00..2,847.30 rows=142,365 width=16) (actual time=0.007..18.062 rows=142,365 loops=1)

11. 88.632 239.391 ↑ 21.3 66,972 3

Hash Join (cost=4,908.21..48,938.40 rows=1,423,650 width=16) (actual time=41.383..79.797 rows=66,972 loops=3)

  • Hash Cond: (all_parent_stock_elements_ids.parent_stock_element_id = stock_element_2.stock_element_id)
12. 42.990 42.990 ↑ 12.4 114,427 3

WorkTable Scan on all_parent_stock_elements_ids (cost=0.00..28,473.00 rows=1,423,650 width=8) (actual time=0.010..14.330 rows=114,427 loops=3)

13. 65.046 107.769 ↑ 1.0 142,365 3

Hash (cost=2,432.65..2,432.65 rows=142,365 width=16) (actual time=35.923..35.923 rows=142,365 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 2630kB
14. 42.723 42.723 ↑ 1.0 142,365 3

Seq Scan on stock_element stock_element_2 (cost=0.00..2,432.65 rows=142,365 width=16) (actual time=0.012..14.241 rows=142,365 loops=3)

15.          

CTE all_stock_element_ids

16. 44.497 679.875 ↑ 19.2 770,376 1

Append (cost=0.00..370,149.00 rows=14,805,960 width=8) (actual time=0.013..679.875 rows=770,376 loops=1)

17. 74.808 74.808 ↑ 1.0 142,365 1

CTE Scan on start_stock_element_ids start_stock_element_ids_2 (cost=0.00..2,847.30 rows=142,365 width=8) (actual time=0.012..74.808 rows=142,365 loops=1)

18. 159.538 159.538 ↑ 1.0 284,730 1

CTE Scan on all_children_stock_elements_ids (cost=0.00..5,694.60 rows=284,730 width=8) (actual time=0.011..159.538 rows=284,730 loops=1)

19. 401.032 401.032 ↑ 41.9 343,281 1

CTE Scan on all_parent_stock_elements_ids all_parent_stock_elements_ids_1 (cost=0.00..287,577.30 rows=14,378,865 width=8) (actual time=0.009..401.032 rows=343,281 loops=1)

20.          

CTE all_stock_element_ids_with_active_properties

21. 22.646 1,603.624 ↓ 2.0 142,365 1

Nested Loop (cost=333,134.52..334,698.10 rows=71,183 width=8) (actual time=1,116.472..1,603.624 rows=142,365 loops=1)

22. 262.997 1,153.883 ↓ 711.8 142,365 1

HashAggregate (cost=333,134.10..333,136.10 rows=200 width=8) (actual time=1,116.436..1,153.883 rows=142,365 loops=1)

  • Group Key: all_stock_element_ids.stock_element_id
23. 890.886 890.886 ↑ 19.2 770,376 1

CTE Scan on all_stock_element_ids (cost=0.00..296,119.20 rows=14,805,960 width=8) (actual time=0.013..890.886 rows=770,376 loops=1)

24. 427.095 427.095 ↑ 1.0 1 142,365

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.003..0.003 rows=1 loops=142,365)

  • 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.355 ms
Execution time : 1,677.994 ms