explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F55A

Settings
# exclusive inclusive rows x rows loops node
1. 25.811 494.510 ↓ 1.0 563 1

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

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

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

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

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

4. 0.009 0.009 ↑ 1.0 1 1

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

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

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

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

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

  • Hash Cond: (wsu.shipment_id = s.shipment_id)
7. 292.530 314.503 ↑ 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=40.949..314.503 rows=82,178 loops=1)

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

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

  • Index Cond: (product_id = 303682)
9. 0.084 1.439 ↓ 1.0 563 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
10. 1.279 1.355 ↓ 1.0 563 1

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

  • Recheck Cond: (product_id = 303682)
  • Heap Blocks: exact=560
11. 0.076 0.076 ↓ 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.076..0.076 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