explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dg94

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

Limit (cost=2,406,957,186.70..2,406,957,186.75 rows=20 width=12) (actual time=3,581.342..3,581.346 rows=20 loops=1)

2.          

CTE ctetable

3. 1.928 19.845 ↑ 1,144.9 14,039 1

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

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

5. 3.321 16.677 ↑ 576.9 2,785 3

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

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

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

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

8. 4.713 6.054 ↑ 8.0 7,079 3

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

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 240kB
9. 1.341 1.341 ↑ 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.447 rows=4,670 loops=3)

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9
10. 31.250 3,581.344 ↑ 38,214.8 20 1

Sort (cost=2,406,331,009.49..2,406,332,920.23 rows=764,297 width=12) (actual time=3,581.340..3,581.344 rows=20 loops=1)

  • Sort Key: products.sum_order_last DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
11. 171.639 3,550.094 ↑ 3.5 221,499 1

Merge Join (cost=0.86..2,406,310,671.82 rows=764,297 width=12) (actual time=23.809..3,550.094 rows=221,499 loops=1)

  • Merge Cond: (mpa.product_id = products.product_id)
12. 69.457 2,150.724 ↑ 3.5 221,499 1

Group (cost=0.43..2,405,207,259.56 rows=764,297 width=8) (actual time=23.793..2,150.724 rows=221,499 loops=1)

  • Group Key: mpa.product_id
13. 2,058.232 2,081.267 ↑ 3.0 402,920 1

Index Scan using product_id_index on wp_pidi_category_map_product_aliexpress mpa (cost=0.43..2,405,204,251.19 rows=1,203,348 width=8) (actual time=23.792..2,081.267 rows=402,920 loops=1)

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

SubPlan (forIndex Scan)

15. 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))
16. 23.035 23.035 ↑ 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.283..23.035 rows=1,027 loops=1)

  • Filter: (parent = 15)
  • Rows Removed by Filter: 13012
17. 1,227.731 1,227.731 ↑ 1.0 1,231,723 1

Index Scan using product_id_aliexpress on wp_pidi_product_aliexpress products (cost=0.43..1,083,136.27 rows=1,231,723 width=12) (actual time=0.011..1,227.731 rows=1,231,723 loops=1)