explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Thx

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 7,423.602 ↑ 1.0 20 1

Limit (cost=1,631,528.80..1,631,528.85 rows=20 width=144) (actual time=7,423.596..7,423.602 rows=20 loops=1)

2.          

CTE ctetable

3. 2.066 20.281 ↑ 1,144.9 14,039 1

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

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

5. 3.450 17.034 ↑ 576.9 2,785 3

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

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

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

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

8. 4.911 6.270 ↑ 8.0 7,079 3

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

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

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9
10. 77.789 7,423.601 ↑ 38,214.8 20 1

Sort (cost=1,005,351.59..1,007,262.33 rows=764,297 width=144) (actual time=7,423.596..7,423.601 rows=20 loops=1)

  • Sort Key: products.sum_order_7 DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 44kB
11. 3,062.565 7,345.812 ↑ 3.5 221,499 1

Hash Join (cost=929,613.13..985,013.92 rows=764,297 width=144) (actual time=4,144.174..7,345.812 rows=221,499 loops=1)

  • Hash Cond: (wp_pidi_category_map_product_aliexpress.product_id = products.product_id)
12. 56.143 776.116 ↑ 3.5 221,499 1

Group (cost=617,231.36..623,248.10 rows=764,297 width=8) (actual time=635.067..776.116 rows=221,499 loops=1)

  • Group Key: wp_pidi_category_map_product_aliexpress.product_id
13. 239.064 719.973 ↑ 3.0 402,920 1

Sort (cost=617,231.36..620,239.73 rows=1,203,348 width=8) (actual time=635.064..719.973 rows=402,920 loops=1)

  • Sort Key: wp_pidi_category_map_product_aliexpress.product_id
  • Sort Method: external merge Disk: 7080kB
14. 457.464 480.909 ↑ 3.0 402,920 1

Seq Scan on wp_pidi_category_map_product_aliexpress (cost=361,848.66..479,248.00 rows=1,203,348 width=8) (actual time=38.042..480.909 rows=402,920 loops=1)

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

SubPlan (forSeq Scan)

16. 23.445 23.445 ↑ 78.3 1,027 1

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

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

Hash (cost=271,725.23..271,725.23 rows=1,231,723 width=144) (actual time=3,507.131..3,507.131 rows=1,231,723 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 256 (originally 64) Memory Usage: 3841kB
18. 829.591 829.591 ↑ 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=144) (actual time=0.179..829.591 rows=1,231,723 loops=1)