explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dm4j

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

Limit (cost=2,407,192,315.48..2,407,192,315.53 rows=20 width=12) (actual time=1,995.965..1,995.969 rows=20 loops=1)

2.          

CTE ctetable

3. 1.900 19.413 ↑ 1,144.9 14,039 1

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

4. 1.271 1.271 ↑ 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.121..1.271 rows=5,683 loops=1)

5. 3.297 16.242 ↑ 576.9 2,785 3

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

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

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

  • Sort Key: s2.parent
  • Sort Method: quicksort Memory: 459kB
7. 2.604 2.604 ↑ 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.868 rows=5,683 loops=3)

8. 4.497 5.853 ↑ 8.0 7,079 3

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

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

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

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9
10. 56.613 1,995.967 ↑ 60,167.4 20 1

Sort (cost=2,406,566,138.27..2,406,569,146.64 rows=1,203,348 width=12) (actual time=1,995.964..1,995.967 rows=20 loops=1)

  • Sort Key: products.sum_order_last DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
11. 210.402 1,939.354 ↑ 3.0 402,920 1

Nested Loop (cost=0.43..2,406,534,117.62 rows=1,203,348 width=12) (actual time=38.508..1,939.354 rows=402,920 loops=1)

12. 497.467 520.192 ↑ 3.0 402,920 1

Seq Scan on wp_pidi_category_map_product_aliexpress mpa (cost=0.00..2,404,921,934.28 rows=1,203,348 width=8) (actual time=38.492..520.192 rows=402,920 loops=1)

  • Filter: ((alternatives: SubPlan 2 or hashed SubPlan 3) OR (category_id = 15))
  • Rows Removed by Filter: 2002903
13.          

SubPlan (forSeq Scan)

14. 0.000 0.000 ↓ 0.0 0

CTE Scan on ctetable (cost=0.00..401,830.83 rows=402 width=0) (never executed)

  • Filter: ((parent = 15) AND (id = mpa.category_id))
15. 22.725 22.725 ↑ 78.3 1,027 1

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

  • Filter: (parent = 15)
  • Rows Removed by Filter: 13012
16. 1,208.760 1,208.760 ↑ 1.0 1 402,920

Index Scan using product_id_aliexpress on wp_pidi_product_aliexpress products (cost=0.43..1.33 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=402,920)

  • Index Cond: (product_id = mpa.product_id)