explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lp2L

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,530.363 ↑ 1.0 20 1

Limit (cost=1,518,491.05..1,518,491.10 rows=20 width=12) (actual time=1,530.359..1,530.363 rows=20 loops=1)

2.          

CTE ctetable

3. 2.074 20.041 ↑ 1,144.9 14,039 1

Recursive Union (cost=0.00..626,177.21 rows=16,073,233 width=16) (actual time=0.107..20.041 rows=14,039 loops=1)

4. 1.275 1.275 ↑ 1.0 5,683 1

Seq Scan on wp_pidi_category_aliexpress s (cost=0.00..351.83 rows=5,683 width=16) (actual time=0.106..1.275 rows=5,683 loops=1)

5. 3.531 16.692 ↑ 576.9 2,785 3

Merge Join (cost=6,306.33..30,436.07 rows=1,606,755 width=16) (actual time=3.774..5.564 rows=2,785 loops=3)

  • Merge Cond: (s2.parent = p.id)
6. 4.467 7.062 ↑ 1.0 5,656 3

Sort (cost=706.23..720.44 rows=5,683 width=16) (actual time=1.903..2.354 rows=5,656 loops=3)

  • Sort Key: s2.parent
  • Sort Method: quicksort Memory: 459kB
7. 2.595 2.595 ↑ 1.0 5,683 3

Seq Scan on wp_pidi_category_aliexpress s2 (cost=0.00..351.83 rows=5,683 width=16) (actual time=0.048..0.865 rows=5,683 loops=3)

8. 4.662 6.099 ↑ 8.0 7,079 3

Sort (cost=5,600.10..5,741.46 rows=56,546 width=16) (actual time=1.440..2.033 rows=7,079 loops=3)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 240kB
9. 1.437 1.437 ↑ 12.1 4,670 3

WorkTable Scan on ctetable p (cost=0.00..1,136.60 rows=56,546 width=16) (actual time=0.002..0.479 rows=4,670 loops=3)

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9
10. 23.092 1,530.361 ↑ 45,478.7 20 1

Sort (cost=892,313.84..894,587.78 rows=909,574 width=12) (actual time=1,530.357..1,530.361 rows=20 loops=1)

  • Sort Key: products.sum_order_last DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
11. 198.219 1,507.269 ↑ 4.1 221,499 1

Hash Join (cost=827,370.46..868,110.40 rows=909,574 width=12) (actual time=1,158.929..1,507.269 rows=221,499 loops=1)

  • Hash Cond: (mpa.product_id = products.product_id)
12. 54.582 411.347 ↑ 4.1 221,499 1

Group (cost=534,233.69..540,248.25 rows=909,574 width=8) (actual time=260.577..411.347 rows=221,499 loops=1)

  • Group Key: mpa.product_id
13. 230.169 356.765 ↑ 3.0 402,920 1

Sort (cost=534,233.69..537,240.97 rows=1,202,912 width=8) (actual time=260.574..356.765 rows=402,920 loops=1)

  • Sort Key: mpa.product_id
  • Sort Method: external merge Disk: 7080kB
14. 38.964 126.596 ↑ 3.0 402,920 1

Nested Loop (cost=363,456.57..396,304.51 rows=1,202,912 width=8) (actual time=23.582..126.596 rows=402,920 loops=1)

15. 0.604 23.896 ↓ 5.1 1,028 1

HashAggregate (cost=363,456.01..363,458.01 rows=200 width=8) (actual time=23.562..23.896 rows=1,028 loops=1)

  • Group Key: ((15)::bigint)
16. 0.069 23.292 ↑ 78.2 1,028 1

Append (cost=0.00..362,451.42 rows=80,367 width=8) (actual time=0.014..23.292 rows=1,028 loops=1)

17. 0.013 0.014 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

18. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

19. 23.209 23.209 ↑ 78.3 1,027 1

CTE Scan on ctetable (cost=0.00..361,647.74 rows=80,366 width=8) (actual time=3.427..23.209 rows=1,027 loops=1)

  • Filter: (parent = 15)
  • Rows Removed by Filter: 13012
20. 63.736 63.736 ↑ 2.8 392 1,028

Index Only Scan using wp_pidi_category_map_product_aliexpress_pkey on wp_pidi_category_map_product_aliexpress mpa (cost=0.56..153.38 rows=1,085 width=16) (actual time=0.004..0.062 rows=392 loops=1,028)

  • Index Cond: (category_id = ((15)::bigint))
  • Heap Fetches: 0
21. 214.349 897.703 ↑ 1.0 1,231,723 1

Hash (cost=271,725.23..271,725.23 rows=1,231,723 width=12) (actual time=897.703..897.703 rows=1,231,723 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2661kB
22. 683.354 683.354 ↑ 1.0 1,231,723 1

Seq Scan on wp_pidi_product_aliexpress products (cost=0.00..271,725.23 rows=1,231,723 width=12) (actual time=0.129..683.354 rows=1,231,723 loops=1)

Planning time : 0.412 ms
Execution time : 1,532.877 ms