explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KCpO

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 8,504.975 ↑ 1.0 60 1

Limit (cost=369,108.69..369,108.72 rows=60 width=20) (actual time=8,504.968..8,504.975 rows=60 loops=1)

2. 0.333 8,504.971 ↑ 8.3 60 1

Sort (cost=369,108.69..369,108.94 rows=500 width=20) (actual time=8,504.968..8,504.971 rows=60 loops=1)

  • Sort Key: (icount((source_collections.collection_ids & destination_collections.collection_ids))) DESC
  • Sort Method: top-N heapsort Memory: 32kB
3. 0.341 8,504.638 ↑ 1.0 500 1

Nested Loop (cost=7,474.57..369,105.24 rows=500 width=20) (actual time=95.268..8,504.638 rows=500 loops=1)

4. 0.405 2.797 ↑ 1.0 50 1

Nested Loop (cost=98.59..303.13 rows=50 width=67) (actual time=0.625..2.797 rows=50 loops=1)

5. 0.226 0.792 ↑ 1.0 50 1

HashAggregate (cost=98.51..98.66 rows=50 width=8) (actual time=0.590..0.792 rows=50 loops=1)

  • Group Key: saves.product_id
6. 0.005 0.566 ↑ 1.0 50 1

Limit (cost=0.14..98.33 rows=50 width=16) (actual time=0.054..0.566 rows=50 loops=1)

7. 0.561 0.561 ↑ 93.4 50 1

Index Scan using index_saves_on_user_id_and_created_at on saves (cost=0.14..9,167.17 rows=4,668 width=16) (actual time=0.054..0.561 rows=50 loops=1)

  • Index Cond: (user_id = 26,354,089)
8. 1.600 1.600 ↑ 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.032..0.032 rows=1 loops=50)

  • Index Cond: (product_id = saves.product_id)
9. 0.300 8,501.500 ↑ 1.0 10 50

Limit (cost=7,375.98..7,375.98 rows=10 width=12) (actual time=170.028..170.030 rows=10 loops=50)

10. 71.700 8,501.200 ↑ 313.2 10 50

Sort (cost=7,375.98..7,377.54 rows=3,132 width=12) (actual time=170.024..170.024 rows=10 loops=50)

  • Sort Key: (icount((source_collections.collection_ids & destination_collections.collection_ids))) DESC
  • Sort Method: top-N heapsort Memory: 25kB
11. 7,509.254 8,429.500 ↓ 2.5 7,804 50

Bitmap Heap Scan on buyable_product_collections destination_collections (cost=100.93..7,362.44 rows=3,132 width=12) (actual time=19.213..168.590 rows=7,804 loops=50)

  • Recheck Cond: (collection_ids && source_collections.collection_ids)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 106
  • Heap Blocks: exact=250,636
12. 919.950 919.950 ↓ 1.5 9,533 50

Bitmap Index Scan on index_buyable_product_collections_on_collection_ids (cost=0.00..23.40 rows=6,264 width=0) (actual time=18.399..18.399 rows=9,533 loops=50)

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

SubPlan (for Bitmap Heap Scan)

14. 0.296 0.296 ↑ 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.067..0.296 rows=164 loops=1)

  • Index Cond: (user_id = 26,354,089)
  • Heap Fetches: 40
Planning time : 0.489 ms
Execution time : 8,505.122 ms