explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jFCx

Settings
# exclusive inclusive rows x rows loops node
1. 20.637 811.432 ↓ 1.5 261 1

Nested Loop Left Join (cost=20,248.46..25,951.72 rows=177 width=110) (actual time=695.531..811.432 rows=261 loops=1)

2.          

CTE review_pool

3. 4.131 685.714 ↑ 425.8 13 1

GroupAggregate (cost=19,897.24..20,118.68 rows=5,536 width=84) (actual time=681.017..685.714 rows=13 loops=1)

  • Group Key: p_1.master_product_id
  • Filter: (count(lr_1.image_url) > 10)
  • Rows Removed by Filter: 1346
4. 3.775 681.583 ↓ 1.0 5,620 1

Sort (cost=19,897.24..19,911.08 rows=5,536 width=61) (actual time=680.783..681.583 rows=5,620 loops=1)

  • Sort Key: p_1.master_product_id
  • Sort Method: quicksort Memory: 539kB
5. 12.856 677.808 ↓ 1.0 5,620 1

Hash Left Join (cost=18,173.73..19,553.05 rows=5,536 width=61) (actual time=617.933..677.808 rows=5,620 loops=1)

  • Hash Cond: (lr_1.product_id = p_1.id)
6. 48.952 48.952 ↓ 1.0 5,620 1

Seq Scan on loox_reviews lr_1 (cost=0.00..849.20 rows=5,536 width=65) (actual time=0.008..48.952 rows=5,620 loops=1)

  • Filter: (rating >= 4)
  • Rows Removed by Filter: 7579
7. 28.104 616.000 ↑ 1.0 66,299 1

Hash (cost=17,020.99..17,020.99 rows=66,299 width=12) (actual time=616.000..616.000 rows=66,299 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2509kB
8. 587.896 587.896 ↑ 1.0 66,299 1

Seq Scan on products p_1 (cost=0.00..17,020.99 rows=66,299 width=12) (actual time=0.977..587.896 rows=66,299 loops=1)

9. 0.432 783.487 ↓ 1.5 261 1

Hash Join (cost=129.35..4,360.07 rows=177 width=86) (actual time=695.181..783.487 rows=261 loops=1)

  • Hash Cond: (p.master_product_id = review_pool.master_product_id)
10. 0.315 97.240 ↓ 1.7 885 1

Nested Loop (cost=0.29..4,229.04 rows=512 width=86) (actual time=2.258..97.240 rows=885 loops=1)

11. 4.885 4.885 ↓ 1.7 885 1

Seq Scan on loox_reviews lr (cost=0.00..849.20 rows=512 width=71) (actual time=1.491..4.885 rows=885 loops=1)

  • Filter: ((image_url IS NOT NULL) AND (rating >= 4))
  • Rows Removed by Filter: 12314
12. 92.040 92.040 ↑ 1.0 1 885

Index Scan using products_pkey on products p (cost=0.29..6.59 rows=1 width=31) (actual time=0.102..0.104 rows=1 loops=885)

  • Index Cond: (id = lr.product_id)
13. 0.011 685.815 ↑ 15.4 13 1

Hash (cost=126.56..126.56 rows=200 width=4) (actual time=685.815..685.815 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.012 685.804 ↑ 15.4 13 1

HashAggregate (cost=124.56..126.56 rows=200 width=4) (actual time=685.802..685.804 rows=13 loops=1)

  • Group Key: review_pool.master_product_id
15. 685.792 685.792 ↑ 425.8 13 1

CTE Scan on review_pool (cost=0.00..110.72 rows=5,536 width=4) (actual time=681.026..685.792 rows=13 loops=1)

16. 7.308 7.308 ↑ 1.0 1 261

Index Scan using orders_document_storage_pkey on orders_document_storage ods (cost=0.43..8.30 rows=1 width=26) (actual time=0.028..0.028 rows=1 loops=261)

  • Index Cond: (lr.order_id = id)
Planning time : 21.531 ms
Execution time : 811.974 ms