explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V4dv : join

Settings
# exclusive inclusive rows x rows loops node
1. 277.088 277.088 ↑ 1.0 142,376 1

CTE Scan on all_stock_element_ids_with_active_properties (cost=27,816.03..30,674.31 rows=142,914 width=8) (actual time=99.414..277.088 rows=142,376 loops=1)

2.          

CTE start_stock_element_ids

3. 0.043 0.043 ↑ 1.0 3 1

Index Scan using stock_element_pkey on stock_element (cost=0.42..17.33 rows=3 width=16) (actual time=0.023..0.043 rows=3 loops=1)

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

CTE all_children_stock_elements_ids

5. 6.783 20.580 ↑ 1.0 142,368 1

Append (cost=0.00..3,144.55 rows=142,368 width=8) (actual time=0.001..20.580 rows=142,368 loops=1)

6. 0.002 0.002 ↑ 1.0 3 1

CTE Scan on start_stock_element_ids (cost=0.00..0.06 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=1)

7. 13.795 13.795 ↑ 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.009..13.795 rows=142,365 loops=1)

8.          

CTE all_parent_stock_elements_ids

9. 0.009 0.048 ↑ 60.6 5 1

Recursive Union (cost=0.00..2,503.37 rows=303 width=16) (actual time=0.002..0.048 rows=5 loops=1)

10. 0.001 0.001 ↑ 1.0 3 1

CTE Scan on start_stock_element_ids start_stock_element_ids_1 (cost=0.00..0.06 rows=3 width=16) (actual time=0.001..0.001 rows=3 loops=1)

11. 0.006 0.038 ↑ 30.0 1 2

Nested Loop (cost=0.42..249.73 rows=30 width=16) (actual time=0.018..0.019 rows=1 loops=2)

12. 0.002 0.002 ↑ 15.0 2 2

WorkTable Scan on all_parent_stock_elements_ids (cost=0.00..0.60 rows=30 width=8) (actual time=0.001..0.001 rows=2 loops=2)

13. 0.030 0.030 ↓ 0.0 0 5

Index Scan using stock_element_pkey on stock_element stock_element_2 (cost=0.42..8.30 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=5)

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

CTE all_stock_element_ids

15. 6.686 60.290 ↑ 1.0 142,376 1

Append (cost=0.00..3,566.85 rows=142,674 width=8) (actual time=0.025..60.290 rows=142,376 loops=1)

16. 0.047 0.047 ↑ 1.0 3 1

CTE Scan on start_stock_element_ids start_stock_element_ids_2 (cost=0.00..0.06 rows=3 width=8) (actual time=0.024..0.047 rows=3 loops=1)

17. 53.507 53.507 ↑ 1.0 142,368 1

CTE Scan on all_children_stock_elements_ids (cost=0.00..2,847.36 rows=142,368 width=8) (actual time=0.002..53.507 rows=142,368 loops=1)

18. 0.050 0.050 ↑ 60.6 5 1

CTE Scan on all_parent_stock_elements_ids all_parent_stock_elements_ids_1 (cost=0.00..6.06 rows=303 width=8) (actual time=0.004..0.050 rows=5 loops=1)

19.          

CTE all_stock_element_ids_with_active_properties

20. 55.496 244.702 ↑ 1.0 142,376 1

Hash Join (cost=12,093.28..18,583.92 rows=142,914 width=8) (actual time=99.412..244.702 rows=142,376 loops=1)

  • Hash Cond: (all_stock_element_ids.stock_element_id = stock_element_property.stock_element_id)
21. 90.347 90.347 ↑ 1.0 142,376 1

CTE Scan on all_stock_element_ids (cost=0.00..2,853.48 rows=142,674 width=8) (actual time=0.026..90.347 rows=142,376 loops=1)

22. 38.769 98.859 ↑ 1.0 142,365 1

Hash (cost=9,756.70..9,756.70 rows=142,366 width=8) (actual time=98.858..98.859 rows=142,365 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3811kB
23. 60.090 60.090 ↑ 1.0 142,365 1

Seq Scan on stock_element_property (cost=0.00..9,756.70 rows=142,366 width=8) (actual time=0.009..60.090 rows=142,365 loops=1)

  • Filter: (is_active_flg AND (NOT is_deleted_flg))
  • Rows Removed by Filter: 188
Planning time : 0.431 ms
Execution time : 282.908 ms