explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iNKV

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

Limit (cost=2,291,360.31..2,291,360.36 rows=20 width=144) (actual time=17,767.494..17,767.501 rows=20 loops=1)

2. 82.185 17,767.499 ↑ 60,167.4 20 1

Sort (cost=2,291,360.31..2,294,368.68 rows=1,203,348 width=144) (actual time=17,767.493..17,767.499 rows=20 loops=1)

  • Sort Key: (max(products.grow_all_order)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 44kB
3. 499.680 17,685.314 ↑ 5.4 221,499 1

GroupAggregate (cost=2,187,050.24..2,259,339.66 rows=1,203,348 width=144) (actual time=16,234.890..17,685.314 rows=221,499 loops=1)

  • Group Key: products.product_id
4. 218.640 17,185.634 ↑ 3.0 402,920 1

Merge Join (cost=2,187,050.24..2,214,214.11 rows=1,203,348 width=495) (actual time=16,234.860..17,185.634 rows=402,920 loops=1)

  • Merge Cond: (products.product_id = mp.product_id)
5. 15,396.986 16,138.889 ↑ 1.0 1,231,723 1

Sort (cost=943,629.85..946,709.15 rows=1,231,723 width=495) (actual time=15,553.637..16,138.889 rows=1,231,723 loops=1)

  • Sort Key: products.product_id
  • Sort Method: external merge Disk: 613048kB
6. 741.903 741.903 ↑ 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=495) (actual time=0.132..741.903 rows=1,231,723 loops=1)

7. 43.956 828.105 ↑ 3.0 402,920 1

Materialize (cost=1,243,408.57..1,249,425.31 rows=1,203,348 width=8) (actual time=681.212..828.105 rows=402,920 loops=1)

8. 290.580 784.149 ↑ 3.0 402,920 1

Sort (cost=1,243,408.57..1,246,416.94 rows=1,203,348 width=8) (actual time=681.205..784.149 rows=402,920 loops=1)

  • Sort Key: mp.product_id
  • Sort Method: external merge Disk: 7080kB
9. 468.882 493.569 ↑ 3.0 402,920 1

Seq Scan on wp_pidi_category_map_product_aliexpress mp (cost=988,025.86..1,105,425.21 rows=1,203,348 width=8) (actual time=39.863..493.569 rows=402,920 loops=1)

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

SubPlan (forSeq Scan)

11. 24.687 24.687 ↑ 78.3 1,027 1

CTE Scan on ctetable (cost=626,177.21..987,824.95 rows=80,366 width=8) (actual time=4.769..24.687 rows=1,027 loops=1)

  • Filter: (parent = 15)
  • Rows Removed by Filter: 13012
12.          

CTE ctetable

13. 2.191 20.790 ↑ 1,144.9 14,039 1

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

14. 1.736 1.736 ↑ 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.166..1.736 rows=5,683 loops=1)

15. 3.312 16.863 ↑ 576.9 2,785 3

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

  • Merge Cond: (s2.parent = p.id)
16. 4.755 7.692 ↑ 1.0 5,656 3

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

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

18. 4.524 5.859 ↑ 8.0 7,079 3

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

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

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9