explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BjbK

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 29.615 3,760.136 ↑ 100.0 2 1

HashAggregate (cost=401,254.31..401,256.31 rows=200 width=20) (actual time=3,760.134..3,760.136 rows=2 loops=1)

  • Group Key: marketplace_upc.marketplace_id
2. 128.736 3,730.521 ↑ 1.5 77,538 1

GroupAggregate (cost=396,742.63..398,998.47 rows=112,792 width=24) (actual time=2,587.395..3,730.521 rows=77,538 loops=1)

  • Group Key: marketplace_upc.provided_upc, marketplace_upc.marketplace_id
3. 2,090.695 3,601.785 ↓ 2.8 316,371 1

Sort (cost=396,742.63..397,024.61 rows=112,792 width=20) (actual time=2,587.361..3,601.785 rows=316,371 loops=1)

  • Sort Key: marketplace_upc.provided_upc, marketplace_upc.marketplace_id
  • Sort Method: external merge Disk: 10,416kB
4. 189.709 1,511.090 ↓ 2.8 316,371 1

Nested Loop Left Join (cost=3,049.91..384,964.02 rows=112,792 width=20) (actual time=9.578..1,511.090 rows=316,371 loops=1)

5. 35.953 80.773 ↓ 1.1 77,538 1

Nested Loop (cost=3,049.48..342,051.09 rows=71,954 width=30) (actual time=9.557..80.773 rows=77,538 loops=1)

6. 0.050 0.050 ↑ 1.0 2 1

Seq Scan on retailer (cost=0.00..7.75 rows=2 width=4) (actual time=0.011..0.050 rows=2 loops=1)

  • Filter: (organization_id = 211)
  • Rows Removed by Filter: 298
7. 36.184 44.770 ↑ 1.9 38,769 2

Bitmap Heap Scan on marketplace_upc (cost=3,049.48..170,282.42 rows=73,925 width=30) (actual time=4.810..22.385 rows=38,769 loops=2)

  • Recheck Cond: (marketplace_id = retailer.marketplace_id)
  • Heap Blocks: exact=6,913
8. 8.586 8.586 ↑ 1.9 38,769 2

Bitmap Index Scan on marketplace_upc_pk (cost=0.00..3,031.00 rows=73,925 width=0) (actual time=4.292..4.293 rows=38,769 loops=2)

  • Index Cond: (marketplace_id = retailer.marketplace_id)
9. 1,240.608 1,240.608 ↑ 1.0 4 77,538

Index Scan using product_ids_gtin_14_index on product_ids _unmatched_upcs (cost=0.43..0.56 rows=4 width=18) (actual time=0.012..0.016 rows=4 loops=77,538)

  • Index Cond: ((marketplace_upc.gtin_14)::text = (gtin_14)::text)
Planning time : 1.137 ms
Execution time : 3,762.463 ms