explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aiCR

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

Limit (cost=1,105,406.18..1,105,406.23 rows=20 width=144) (actual time=3,373.867..3,373.873 rows=20 loops=1)

2. 81.260 3,373.870 ↑ 60,275.9 20 1

Sort (cost=1,105,406.18..1,108,419.98 rows=1,205,518 width=144) (actual time=3,373.865..3,373.870 rows=20 loops=1)

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

GroupAggregate (cost=283,645.89..1,073,327.78 rows=1,205,518 width=144) (actual time=11.550..3,292.610 rows=221,499 loops=1)

  • Group Key: products.product_id
4. 281.591 2,781.799 ↑ 3.0 402,920 1

Merge Join (cost=283,645.89..1,028,120.86 rows=1,205,518 width=495) (actual time=11.518..2,781.799 rows=402,920 loops=1)

  • Merge Cond: (products.product_id = mp.product_id)
5. 672.053 672.053 ↓ 1.0 1,231,723 1

Index Scan using product_id_aliexpress on wp_pidi_product_aliexpress products (cost=0.43..603,884.82 rows=1,231,003 width=495) (actual time=0.005..672.053 rows=1,231,723 loops=1)

6. 1,817.027 1,828.155 ↑ 3.0 402,920 1

Index Scan using product_id_index on wp_pidi_category_map_product_aliexpress mp (cost=283,645.47..410,180.98 rows=1,205,518 width=8) (actual time=11.507..1,828.155 rows=402,920 loops=1)

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

SubPlan (forIndex Scan)

8. 11.128 11.128 ↑ 21.8 1,027 1

CTE Scan on ctetable (cost=182,693.97..283,588.98 rows=22,421 width=8) (actual time=3.002..11.128 rows=1,027 loops=1)

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

CTE ctetable

10. 2.090 7.939 ↑ 319.4 14,039 1

Recursive Union (cost=0.00..182,693.97 rows=4,484,223 width=16) (actual time=0.007..7.939 rows=14,039 loops=1)

11. 0.959 0.959 ↑ 1.0 5,683 1

Seq Scan on wp_pidi_category_aliexpress s (cost=0.00..201.83 rows=5,683 width=16) (actual time=0.005..0.959 rows=5,683 loops=1)

12. 2.222 4.890 ↑ 160.8 2,785 3

Hash Join (cost=272.87..9,280.77 rows=447,854 width=16) (actual time=0.702..1.630 rows=2,785 loops=3)

  • Hash Cond: (p.id = s2.parent)
13. 1.092 1.092 ↑ 12.1 4,670 3

WorkTable Scan on ctetable p (cost=0.00..1,136.60 rows=56,546 width=16) (actual time=0.000..0.364 rows=4,670 loops=3)

  • Filter: (parent IS NOT NULL)
  • Rows Removed by Filter: 9
14. 0.785 1.576 ↑ 1.0 5,655 1

Hash (cost=201.83..201.83 rows=5,683 width=16) (actual time=1.576..1.576 rows=5,655 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 330kB
15. 0.791 0.791 ↑ 1.0 5,683 1

Seq Scan on wp_pidi_category_aliexpress s2 (cost=0.00..201.83 rows=5,683 width=16) (actual time=0.002..0.791 rows=5,683 loops=1)