explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YhlT

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 15.069 296.025 ↓ 1.0 563 1

GroupAggregate (cost=225,051.08..225,065.08 rows=560 width=200) (actual time=294.611..296.025 rows=563 loops=1)

  • Group Key: s.shipment_id, p.is_fractional
2. 17.585 280.956 ↓ 146.8 82,234 1

Sort (cost=225,051.08..225,052.48 rows=560 width=200) (actual time=278.356..280.956 rows=82,234 loops=1)

  • Sort Key: s.shipment_id, p.is_fractional
  • Sort Method: quicksort Memory: 9495kB
3. 7.961 263.371 ↓ 146.8 82,234 1

Nested Loop (cost=3,285.47..225,025.52 rows=560 width=200) (actual time=32.597..263.371 rows=82,234 loops=1)

4. 0.008 0.008 ↑ 1.0 1 1

Index Scan using product_pkey on product p (cost=0.42..4.44 rows=1 width=5) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (product_id = 303682)
5. 18.919 255.402 ↓ 146.8 82,234 1

Nested Loop Left Join (cost=3,285.05..225,015.48 rows=560 width=199) (actual time=32.587..255.402 rows=82,234 loops=1)

  • Join Filter: ((s.document_id <> 4) OR (b.ns_path[2] <> 228))
6. 14.785 154.249 ↓ 146.8 82,234 1

Hash Right Join (cost=3,284.63..224,713.79 rows=560 width=23) (actual time=32.571..154.249 rows=82,234 loops=1)

  • Hash Cond: (wsu.shipment_id = s.shipment_id)
7. 121.543 137.326 ↑ 1.5 82,178 1

Bitmap Heap Scan on su wsu (cost=2,353.52..223,333.01 rows=119,678 width=15) (actual time=30.422..137.326 rows=82,178 loops=1)

  • Recheck Cond: (product_id = 303682)
  • Filter: (NOT is_blocked)
  • Rows Removed by Filter: 92922
  • Heap Blocks: exact=89511
8. 15.783 15.783 ↓ 1.0 179,449 1

Bitmap Index Scan on fki_su_product_id_fkey (cost=0.00..2,323.60 rows=174,271 width=0) (actual time=15.783..15.783 rows=179,449 loops=1)

  • Index Cond: (product_id = 303682)
9. 0.067 2.138 ↓ 1.0 563 1

Hash (cost=924.11..924.11 rows=560 width=16) (actual time=2.138..2.138 rows=563 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
10. 2.003 2.071 ↓ 1.0 563 1

Bitmap Heap Scan on shipment s (cost=8.77..924.11 rows=560 width=16) (actual time=0.119..2.071 rows=563 loops=1)

  • Recheck Cond: (product_id = 303682)
  • Heap Blocks: exact=560
11. 0.068 0.068 ↓ 1.0 563 1

Bitmap Index Scan on fki_shipment_product_id_fkey (cost=0.00..8.62 rows=560 width=0) (actual time=0.068..0.068 rows=563 loops=1)

  • Index Cond: (product_id = 303682)
12. 82.234 82.234 ↓ 0.0 0 82,234

Index Scan using box_pkey on box b (cost=0.42..0.52 rows=1 width=213) (actual time=0.001..0.001 rows=0 loops=82,234)

  • Index Cond: (box_id = wsu.box_id)
  • Filter: (box_type_id = ANY ('{31,32,33}'::integer[]))
  • Rows Removed by Filter: 1