explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SJA4

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,776.851 ↑ 1.0 20 1

Limit (cost=2,407,002,815.29..2,407,002,815.34 rows=20 width=112) (actual time=3,776.845..3,776.851 rows=20 loops=1)

2.          

CTE ctetable

3. 1.887 19.567 ↑ 1,144.9 14,039 1

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

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

5. 3.174 16.416 ↑ 576.9 2,785 3

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

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

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

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

8. 4.752 6.084 ↑ 8.0 7,079 3

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

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

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

Sort (cost=2,406,376,638.09..2,406,379,646.46 rows=1,203,348 width=112) (actual time=3,776.844..3,776.848 rows=20 loops=1)

  • Sort Key: (max(products.sum_order_last)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 35kB
11. 382.320 3,699.584 ↑ 5.4 221,499 1

GroupAggregate (cost=0.86..2,406,344,617.43 rows=1,203,348 width=112) (actual time=23.493..3,699.584 rows=221,499 loops=1)

  • Group Key: products.product_id
12. 182.105 3,317.264 ↑ 3.0 402,920 1

Merge Join (cost=0.86..2,406,305,508.62 rows=1,203,348 width=112) (actual time=23.466..3,317.264 rows=402,920 loops=1)

  • Merge Cond: (products.product_id = mpa.product_id)
13. 1,235.796 1,235.796 ↑ 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=112) (actual time=0.015..1,235.796 rows=1,231,723 loops=1)

14. 1,876.609 1,899.363 ↑ 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.447..1,899.363 rows=402,920 loops=1)

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

SubPlan (forIndex Scan)

16. 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))
17. 22.754 22.754 ↑ 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.227..22.754 rows=1,027 loops=1)

  • Filter: (parent = 15)
  • Rows Removed by Filter: 13012
Planning time : 0.431 ms
Execution time : 3,777.586 ms