explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nUnJ

Settings
# exclusive inclusive rows x rows loops node
1. 8,137.251 853,422.768 ↓ 348.9 94,202 1

Nested Loop Left Join (cost=47,797.11..72,865.02 rows=270 width=1,409) (actual time=101.506..853,422.768 rows=94,202 loops=1)

  • Join Filter: (o.vendor_id = vi.vendor_id)
  • Rows Removed by Join Filter: 23602
  • rows=90896 loops=1)
2.          

CTE ordertmp

3. 9,602.013 9,602.013 ↑ 1.5 367,951 1

Seq Scan on "order" (cost=0.00..47,762.28 rows=561,919 width=299) (actual time=0.133..9,602.013 rows=367,951 loops=1)

  • Filter: (deliverylocation_id = 24)
  • Rows Removed by Filter: 32001
4. 9,612.346 809,582.959 ↓ 348.9 94,202 1

Nested Loop Left Join (cost=34.40..23,692.34 rows=270 width=793) (actual time=100.464..809,582.959 rows=94,202 loops=1)

  • Join Filter: (i.flowtype_id = ft.id)
  • Rows Removed by Join Filter: 376808
5. 5,024.266 792,905.463 ↓ 348.9 94,202 1

Nested Loop (cost=34.40..23,671.03 rows=270 width=791) (actual time=100.193..792,905.463 rows=94,202 loops=1)

  • Join Filter: (od.item_id = i.id)
6. 2,562.691 714,309.435 ↓ 348.9 94,202 1

Hash Left Join (cost=33.97..22,276.28 rows=270 width=753) (actual time=84.103..714,309.435 rows=94,202 loops=1)

  • Hash Cond: (o.salestore_id = loss.id)
7. 4,867.135 711,733.987 ↓ 348.9 94,202 1

Nested Loop (cost=18.04..22,256.63 rows=270 width=737) (actual time=71.237..711,733.987 rows=94,202 loops=1)

  • Join Filter: (os.orderstatetype_id = rost.id)
  • Rows Removed by Join Filter: 191382
8. 0.160 0.160 ↑ 1.0 1 1

Seq Scan on orderstatetype rost (cost=0.00..1.09 rows=1 width=8) (actual time=0.086..0.160 rows=1 loops=1)

  • Filter: ((code)::text = 'LIBERADA'::text)
  • Rows Removed by Filter: 6
9. 38,667.478 706,866.692 ↓ 150.9 285,584 1

Nested Loop (cost=18.04..22,231.88 rows=1,893 width=745) (actual time=71.051..706,866.692 rows=285,584 loops=1)

  • Join Filter: (o.id = os.order_id)
  • -> Index Scan using orderstate_order_id_idx on orderstate os (cost=0.43..12.09 rows=4 width=24) (actual time=0.196..0.306 rows=3 loops=9
10. 2,571.866 668,199.214 ↓ 190.3 94,190 1

Hash Join (cost=17.61..16,221.33 rows=495 width=753) (actual time=61.699..668,199.214 rows=94,190 loops=1)

  • Hash Cond: (pd.location_id = ld.id)
  • Index Cond: (order_id = pd.order_id)
11. 341,293.516 665,612.042 ↓ 190.3 94,190 1

Nested Loop (cost=1.68..16,198.59 rows=495 width=737) (actual time=46.057..665,612.042 rows=94,190 loops=1)

  • Join Filter: (o.id = pd.order_id)
  • -> Index Scan using predeliverydetail_order_id_item_id_location_id_idx on predeliverydetail pd (cost=0.56..5.54 rows=1 width
12. 118,926.775 324,318.526 ↓ 730.2 94,190 1

Nested Loop (cost=1.12..15,481.73 rows=129 width=681) (actual time=30.308..324,318.526 rows=94,190 loops=1)

  • -> Index Only Scan using orderdetail_order_id_item_id_outreceivedunits_finalcost_idx on orderdetail od (cost=0.56..6.8
  • Index Cond: ((order_id = od.order_id) AND (item_id = od.item_id))
13. 8,092.407 205,391.751 ↓ 90,896.0 90,896 1

Nested Loop (cost=0.56..15,473.63 rows=1 width=657) (actual time=1.695..205,391.751 rows=90,896 loops=1)

  • -> Index Scan using location_id_idx on location le (cost=0.27..8.29 rows=1 width=32) (actual time=0.025..0.038 r
  • Index Cond: (order_id = o.id)
  • Heap Fetches: 58281
14. 149,670.713 197,299.344 ↓ 90,896.0 90,896 1

Nested Loop (cost=0.29..15,465.33 rows=1 width=641) (actual time=1.515..197,299.344 rows=90,896 loops=1)

  • Join Filter: (o.department_id = d.id)
  • Rows Removed by Join Filter: 5635552
  • -> Seq Scan on department d (cost=0.00..1.63 rows=63 width=25) (actual time=0.021..0.805 rows=63 loops=908
  • Index Cond: (id = 24)
15. 38,629.927 47,628.631 ↓ 90,896.0 90,896 1

Nested Loop (cost=0.29..15,462.91 rows=1 width=632) (actual time=0.777..47,628.631 rows=90,896 loops=1)

  • -> Nested Loop (cost=0.00..15454.60 rows=1 width=606) (actual time=0.417..38996.522 rows=90896 loops
  • Join Filter: (o.currentstatetype_id = ost.id)
  • Rows Removed by Join Filter: 545376
  • -> CTE Scan on ordertmp o (cost=0.00..15452.77 rows=7 width=604) (actual time=0.188..18356.052
  • Filter: (vevcd AND (originaldeliverydate >= '2019-01-01 00:00:00'::timestamp without time
  • Rows Removed by Filter: 277055
  • -> Index Scan using vendor_id_idx on vendor v (cost=0.29..8.30 rows=1 width=34) (actual time=0.028..
  • Index Cond: (id = o.vendor_id)
16. 8,998.704 8,998.704 ↑ 1.0 7 90,896

Materialize (cost=0.00..1.10 rows=7 width=18) (actual time=0.012..0.099 rows=7 loops=90,896)

  • -> Seq Scan on orderstatetype ost (cost=0.00..1.07 rows=7 width=18) (actual time=0.039..
17. 7.553 15.306 ↓ 1.1 443 1

Hash (cost=10.97..10.97 rows=397 width=32) (actual time=15.306..15.306 rows=443 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
18. 7.753 7.753 ↓ 1.1 443 1

Seq Scan on location ld (cost=0.00..10.97 rows=397 width=32) (actual time=0.155..7.753 rows=443 loops=1)

19. 6.456 12.757 ↓ 1.1 443 1

Hash (cost=10.97..10.97 rows=397 width=32) (actual time=12.757..12.757 rows=443 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
20. 6.301 6.301 ↓ 1.1 443 1

Seq Scan on location loss (cost=0.00..10.97 rows=397 width=32) (actual time=0.035..6.301 rows=443 loops=1)

21. 73,571.762 73,571.762 ↑ 1.0 1 94,202

Index Scan using item_id_idx on item i (cost=0.43..5.15 rows=1 width=54) (actual time=0.768..0.781 rows=1 loops=94,202)

  • Index Cond: (id = pd.item_id)
22. 7,065.070 7,065.150 ↑ 1.0 5 94,202

Materialize (cost=0.00..1.07 rows=5 width=18) (actual time=0.013..0.075 rows=5 loops=94,202)

23. 0.080 0.080 ↑ 1.0 5 1

Seq Scan on flowtype ft (cost=0.00..1.05 rows=5 width=18) (actual time=0.028..0.080 rows=5 loops=1)

24. 35,702.558 35,702.558 ↑ 1.0 1 94,202

Index Scan using vendoritem_item_id_vendor_id_idx on vendoritem vi (cost=0.43..5.20 rows=1 width=26) (actual time=0.341..0.379 rows=1 loops=94,202)

  • Index Cond: (i.id = item_id)