explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bck3 : Lots query with source_system_id

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 703.438 ↓ 12.5 100 1

Limit (cost=347,536.5..347,536.52 rows=8 width=391) (actual time=703.423..703.438 rows=100 loops=1)

2. 8.056 703.430 ↓ 12.5 100 1

Sort (cost=347,536.5..347,536.52 rows=8 width=391) (actual time=703.422..703.43 rows=100 loops=1)

  • Sort Key: (make_lot_index(lots_raw.source_system_id, lots_raw.lot_code))
  • Sort Method: top-N heapsort Memory: 106kB
3. 36.689 695.374 ↓ 737.4 5,899 1

Nested Loop (cost=23,460.84..347,536.38 rows=8 width=391) (actual time=54.681..695.374 rows=5,899 loops=1)

4. 3.664 587.897 ↓ 2,949.5 5,899 1

Nested Loop (cost=23,460.28..347,494.01 rows=2 width=279) (actual time=54.571..587.897 rows=5,899 loops=1)

5. 10.645 554.738 ↓ 2,949.5 5,899 1

Nested Loop (cost=23,459.85..347,492.8 rows=2 width=255) (actual time=54.555..554.738 rows=5,899 loops=1)

6. 3.858 502.800 ↓ 2,949.5 5,899 1

Nested Loop (cost=23,459.42..347,491.28 rows=2 width=251) (actual time=54.525..502.8 rows=5,899 loops=1)

7. 0.003 0.045 ↑ 1.0 1 1

Nested Loop (cost=0..5.72 rows=1 width=18) (actual time=0.04..0.045 rows=1 loops=1)

8. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on facility_warehouse_link fwl (cost=0..4.54 rows=1 width=16) (actual time=0.036..0.041 rows=1 loops=1)

  • Filter: ((source_system_id = 3) AND ((facility_id)::integer = 41))
9. 0.001 0.001 ↑ 1.1 7 1

Seq Scan on wms_systems ws (cost=0..1.08 rows=8 width=10) (actual time=0.001..0.001 rows=7 loops=1)

10. 212.843 498.897 ↓ 1.9 11,798 1

Hash Join (cost=23,459.42..347,374.87 rows=6,325 width=256) (actual time=54.478..498.897 rows=11,798 loops=1)

11. 238.978 283.132 ↑ 1.0 628,551 1

Bitmap Heap Scan on lots_raw lots_raw (cost=22,646.51..341,754.89 rows=632,510 width=242) (actual time=50.766..283.132 rows=628,551 loops=1)

12. 44.154 44.154 ↓ 1.0 636,968 1

Bitmap Index Scan on lots_raw_source_system_id_customer_code_idx (cost=0..22,488.38 rows=632,510 width=0) (actual time=44.154..44.154 rows=636,968 loops=1)

  • Index Cond: (source_system_id = 3)
13. 0.011 2.922 ↓ 1.5 3 1

Hash (cost=812.89..812.89 rows=2 width=21) (actual time=2.922..2.922 rows=3 loops=1)

14. 2.911 2.911 ↓ 1.5 3 1

Seq Scan on sfdc_xref_customer cx (cost=0..812.89 rows=2 width=21) (actual time=1.507..2.911 rows=3 loops=1)

  • Filter: ((lin_customer_enterprise_id__c)::text = '14053'::text)
15. 41.293 41.293 ↑ 1.0 1 5,899

Index Scan using items_raw_pkey on items_raw items_raw (cost=0.43..0.76 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=5,899)

  • Index Cond: (concat_ws('_'::text, lots_raw.source_system_id, lots_raw.item_code) = (id)::text)
16. 29.495 29.495 ↓ 0.0 0 5,899

Index Scan using expected_lots_raw_pkey on expected_lots_raw expected (cost=0.42..0.61 rows=1 width=39) (actual time=0.005..0.005 rows=0 loops=5,899)

  • Index Cond: ((lots_raw.source_system_id = source_system_id) AND (source_system_id = 3) AND ((lots_raw.lot_code)::text = (lot_code)::text))
17. 0.000 70.788 ↓ 0.0 0 5,899

Aggregate (cost=0.56..19.99 rows=4 width=30) (actual time=0.012..0.012 rows=0 loops=5,899)

18. 70.788 70.788 ↓ 0.0 0 5,899

Index Scan using pallets_raw_source_system_id_lot_code_idx on pallets_raw pallets_raw (cost=0.56..19.91 rows=4 width=22) (actual time=0.012..0.012 rows=0 loops=5,899)

  • Index Cond: ((lots_raw.source_system_id = source_system_id) AND ((lots_raw.lot_code)::text = (lot_code)::text))
  • Filter: ((hold_code IS NOT NULL) AND (btrim((hold_code)::text) <> ''::text) AND (btrim((hold_code)::text) <> 'NONE'::text))