explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3rsuV

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 17,948.451 ↑ 1.0 60 1

Limit (cost=2,718.77..2,718.80 rows=60 width=20) (actual time=17,948.440..17,948.451 rows=60 loops=1)

2. 144.838 17,948.444 ↑ 870.0 60 1

Sort (cost=2,718.77..2,744.87 rows=52,197 width=20) (actual time=17,948.439..17,948.444 rows=60 loops=1)

  • Sort Key: (icount((source_collections.collection_ids & destination_collections.collection_ids))) DESC
  • Sort Method: top-N heapsort Memory: 30kB
3. 12,017.791 17,803.606 ↓ 8.4 438,674 1

Nested Loop (cost=88.30..2,358.25 rows=52,197 width=20) (actual time=9.524..17,803.606 rows=438,674 loops=1)

4. 0.740 8.615 ↓ 2.9 50 1

Nested Loop (cost=1.20..205.80 rows=17 width=67) (actual time=0.359..8.615 rows=50 loops=1)

5. 0.375 0.675 ↑ 1.0 50 1

HashAggregate (cost=1.12..1.27 rows=50 width=8) (actual time=0.329..0.675 rows=50 loops=1)

  • Group Key: saves.product_id
6. 0.007 0.300 ↑ 1.0 50 1

Limit (cost=0.14..0.94 rows=50 width=8) (actual time=0.069..0.300 rows=50 loops=1)

7. 0.293 0.293 ↑ 93.4 50 1

Index Only Scan using index_saves_on_user_id_and_product_id_and_collection_id on saves (cost=0.14..75.05 rows=4,668 width=8) (actual time=0.069..0.293 rows=50 loops=1)

  • Index Cond: (user_id = 26,354,089)
  • Heap Fetches: 14
8. 7.200 7.200 ↑ 1.0 1 50

Index Scan using index_buyable_product_collections_on_product_id on buyable_product_collections source_collections (cost=0.08..4.09 rows=1 width=67) (actual time=0.144..0.144 rows=1 loops=50)

  • Index Cond: (product_id = saves.product_id)
  • Filter: (icount(collection_ids) > 10)
9. 3,348.781 5,777.200 ↓ 2.8 8,773 50

Bitmap Heap Scan on buyable_product_collections destination_collections (cost=87.09..114.15 rows=3,132 width=67) (actual time=49.714..115.544 rows=8,773 loops=50)

  • Recheck Cond: (collection_ids && source_collections.collection_ids)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 111
  • Heap Blocks: exact=280,199
10. 2,427.850 2,427.850 ↓ 1.7 10,761 50

Bitmap Index Scan on index_buyable_product_collections_on_collection_ids (cost=0.00..9.55 rows=6,264 width=0) (actual time=48.557..48.557 rows=10,761 loops=50)

  • Index Cond: (collection_ids && source_collections.collection_ids)
11.          

SubPlan (for Bitmap Heap Scan)

12. 0.569 0.569 ↑ 28.5 164 1

Index Only Scan using index_saves_on_user_id_and_product_id_and_collection_id on saves saves_1 (cost=0.14..75.05 rows=4,668 width=8) (actual time=0.042..0.569 rows=164 loops=1)

  • Index Cond: (user_id = 26,354,089)
  • Heap Fetches: 40
Planning time : 1.472 ms
Execution time : 17,948.717 ms