explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2TGZ

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

Limit (cost=1,556,981.05..1,556,981.10 rows=20 width=144) (actual time=7,144.937..7,144.947 rows=20 loops=1)

2.          

CTE ctetable

3. 1.919 19.773 ↑ 1,144.9 14,039 1

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

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

5. 3.411 16.635 ↑ 576.9 2,785 3

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

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

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

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

8. 4.668 6.036 ↑ 8.0 7,079 3

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

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

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

Sort (cost=930,803.84..933,077.78 rows=909,574 width=144) (actual time=7,144.936..7,144.946 rows=20 loops=1)

  • Sort Key: products.grow_all_order DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 44kB
11. 3,189.132 7,068.305 ↑ 4.1 221,499 1

Hash Join (cost=846,615.46..906,600.40 rows=909,574 width=144) (actual time=3,737.060..7,068.305 rows=221,499 loops=1)

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

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

  • Group Key: mpa.product_id
13. 216.579 343.468 ↑ 3.0 402,920 1

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

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

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

15. 0.587 23.771 ↓ 5.1 1,028 1

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

  • Group Key: ((15)::bigint)
16. 0.081 23.184 ↑ 78.2 1,028 1

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

17. 0.008 0.009 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.009 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.094 23.094 ↑ 78.3 1,027 1

CTE Scan on ctetable (cost=0.00..361,647.74 rows=80,366 width=8) (actual time=3.372..23.094 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. 2,715.899 3,474.807 ↑ 1.0 1,231,723 1

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

  • Buckets: 32768 (originally 32768) Batches: 256 (originally 64) Memory Usage: 3841kB
22. 758.908 758.908 ↑ 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.129..758.908 rows=1,231,723 loops=1)

Planning time : 0.434 ms
Execution time : 7,147.253 ms