explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nQPx : Original query

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 11.226 ↑ 1.0 11 1

Limit (cost=4.56..18.45 rows=11 width=8) (actual time=0.611..11.226 rows=11 loops=1)

  • Output: "Alias".id, "Alias".product_id
  • Buffers: shared hit=864
2. 0.061 11.209 ↑ 129,872.7 11 1

Merge Semi Join (cost=4.56..1,803,506.69 rows=1,428,600 width=8) (actual time=0.610..11.209 rows=11 loops=1)

  • Output: "Alias".id, "Alias".product_id
  • Merge Cond: ("Alias".id = review.id)
  • Buffers: shared hit=864
3. 0.072 0.072 ↑ 7,246.4 207 1

Index Scan Backward using review_pkey on stuff.review "Alias" (cost=0.43..15,253.43 rows=1,500,000 width=8) (actual time=0.007..0.072 rows=207 loops=1)

  • Output: "Alias".id, "Alias".product_id
  • Buffers: shared hit=7
4. 2.278 11.076 ↑ 129,872.7 11 1

Nested Loop Semi Join (cost=4.13..1,778,074.56 rows=1,428,600 width=4) (actual time=0.596..11.076 rows=11 loops=1)

  • Output: review.id
  • Buffers: shared hit=857
5. 0.104 0.104 ↑ 7,246.4 207 1

Index Scan Backward using review_pkey on stuff.review (cost=0.43..15,253.43 rows=1,500,000 width=8) (actual time=0.004..0.104 rows=207 loops=1)

  • Output: review.id, review.product_id
  • Buffers: shared hit=7
6. 0.621 8.694 ↓ 0.0 0 207

Hash Join (cost=3.71..6.05 rows=3 width=4) (actual time=0.042..0.042 rows=0 loops=207)

  • Output: category_to_product.product_id
  • Hash Cond: ("category_product_Alias".id = category_to_product.category_id)
  • Buffers: shared hit=850
7. 5.971 6.003 ↑ 76.0 1 207

Seq Scan on stuff.category "category_product_Alias" (cost=3.35..5.40 rows=76 width=4) (actual time=0.009..0.029 rows=1 loops=207)

  • Output: "category_product_Alias".id, "category_product_Alias".parent_id
  • Filter: (("category_product_Alias".id = 27) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 142
  • Buffers: shared hit=208
8.          

SubPlan (forSeq Scan)

9. 0.000 0.032 ↓ 0.0 0 1

Nested Loop (cost=0.00..3.35 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: category.id
  • Buffers: shared hit=1
10. 0.032 0.032 ↓ 0.0 0 1

Seq Scan on stuff.category (cost=0.00..1.68 rows=1 width=8) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: category.id, category.parent_id
  • Filter: (category.parent_id = 27)
  • Rows Removed by Filter: 150
  • Buffers: shared hit=1
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on stuff.category "category_category_product_Alias" (cost=0.00..1.68 rows=1 width=4) (never executed)

  • Output: "category_category_product_Alias".id, "category_category_product_Alias".parent_id
  • Filter: ("category_category_product_Alias".id = 27)
12. 0.414 2.070 ↑ 1.0 6 207

Hash (cost=0.33..0.33 rows=6 width=8) (actual time=0.010..0.010 rows=6 loops=207)

  • Output: category_to_product.category_id, category_to_product.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=642
13. 1.656 1.656 ↑ 1.0 6 207

Index Only Scan using category_to_product_pkey on stuff.category_to_product (cost=0.29..0.33 rows=6 width=8) (actual time=0.005..0.008 rows=6 loops=207)

  • Output: category_to_product.category_id, category_to_product.product_id
  • Index Cond: (category_to_product.product_id = review.product_id)
  • Heap Fetches: 1290
  • Buffers: shared hit=642