explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xY3f

Settings
# exclusive inclusive rows x rows loops node
1. 930.332 9,699.948 ↓ 5.9 551,104 1

GroupAggregate (cost=511,605.30..518,130.00 rows=93,210 width=68) (actual time=8,591.844..9,699.948 rows=551,104 loops=1)

2. 1,230.880 8,769.616 ↓ 5.9 551,373 1

Sort (cost=511,605.30..511,838.32 rows=93,210 width=68) (actual time=8,591.794..8,769.616 rows=551,373 loops=1)

  • Sort Key: sl.categ_id, sl.id, lbl.material_id, lbl.date_rchqty, prd.life_time
  • Sort Method: external merge Disk: 30472kB
3. 132.978 7,538.736 ↓ 5.9 551,373 1

Hash Join (cost=491,192.30..503,911.65 rows=93,210 width=68) (actual time=6,806.686..7,538.736 rows=551,373 loops=1)

  • Hash Cond: (lbl.material_id = prd.id)
4. 254.415 7,400.959 ↓ 5.9 551,373 1

Merge Left Join (cost=490,976.52..502,181.21 rows=93,210 width=64) (actual time=6,801.869..7,400.959 rows=551,373 loops=1)

  • Merge Cond: ((lbl.material_id = sl.material_id) AND (lb.partner_id = sl.partner_id))
  • Join Filter: ((sl.categ_id = lb.categ_id) OR (lbl.rsrv_qty > 0::numeric))
  • Rows Removed by Join Filter: 442
5. 100.048 1,160.647 ↑ 1.1 87,955 1

Sort (cost=81,495.90..81,728.93 rows=93,210 width=32) (actual time=1,145.475..1,160.647 rows=87,955 loops=1)

  • Sort Key: lbl.material_id, lb.partner_id
  • Sort Method: quicksort Memory: 9944kB
6. 153.296 1,060.599 ↑ 1.1 87,955 1

Hash Join (cost=27,992.77..73,802.26 rows=93,210 width=32) (actual time=460.426..1,060.599 rows=87,955 loops=1)

  • Hash Cond: (lbl.laborder_id = lb.id)
7. 447.358 447.358 ↑ 1.1 87,955 1

Seq Scan on inno_laborder_material lbl (cost=0.00..43,479.24 rows=93,210 width=28) (actual time=0.022..447.358 rows=87,955 loops=1)

  • Filter: ((rsrv_qty1 > 0::numeric) OR (rsrv_qty > 0::numeric))
  • Rows Removed by Filter: 1228670
8. 190.685 459.945 ↓ 1.0 647,904 1

Hash (cost=19,915.12..19,915.12 rows=646,212 width=12) (actual time=459.945..459.945 rows=647,904 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 27840kB
9. 269.260 269.260 ↓ 1.0 647,904 1

Seq Scan on inno_laborder lb (cost=0.00..19,915.12 rows=646,212 width=12) (actual time=0.016..269.260 rows=647,904 loops=1)

10. 105.169 5,985.897 ↓ 1.0 1,059,291 1

Materialize (cost=409,480.62..414,666.55 rows=1,037,186 width=48) (actual time=5,656.261..5,985.897 rows=1,059,291 loops=1)

11. 1,906.478 5,880.728 ↑ 1.5 697,707 1

Sort (cost=409,480.62..412,073.58 rows=1,037,186 width=48) (actual time=5,656.251..5,880.728 rows=697,707 loops=1)

  • Sort Key: sl.material_id, sl.partner_id
  • Sort Method: external merge Disk: 23192kB
12. 111.567 3,974.250 ↑ 1.5 698,046 1

Subquery Scan on sl (cost=237,636.22..273,937.73 rows=1,037,186 width=48) (actual time=2,933.731..3,974.250 rows=698,046 loops=1)

13. 544.892 3,862.683 ↑ 1.5 698,046 1

GroupAggregate (cost=237,636.22..263,565.87 rows=1,037,186 width=21) (actual time=2,933.730..3,862.683 rows=698,046 loops=1)

14. 1,425.289 3,317.791 ↓ 1.0 1,073,459 1

Sort (cost=237,636.22..240,229.19 rows=1,037,186 width=21) (actual time=2,933.720..3,317.791 rows=1,073,459 loops=1)

  • Sort Key: so.id, so.partner_id, pt.categ_id, irch.material_id
  • Sort Method: external merge Disk: 32920kB
15. 750.293 1,892.502 ↓ 1.0 1,073,459 1

Hash Join (cost=25,913.29..112,726.34 rows=1,037,186 width=21) (actual time=256.360..1,892.502 rows=1,073,459 loops=1)

  • Hash Cond: (soi.order_id = so.id)
16. 708.151 893.108 ↓ 1.0 1,073,459 1

Hash Join (cost=609.51..65,382.35 rows=1,037,186 width=17) (actual time=6.983..893.108 rows=1,073,459 loops=1)

  • Hash Cond: (soi.product_id = prd.id)
17. 177.991 177.991 ↓ 1.0 1,180,635 1

Seq Scan on sale_order_line soi (cost=0.00..47,090.99 rows=1,169,599 width=8) (actual time=0.005..177.991 rows=1,180,635 loops=1)

18. 0.701 6.966 ↓ 1.0 2,716 1

Hash (cost=575.62..575.62 rows=2,711 width=21) (actual time=6.966..6.966 rows=2,716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
19. 1.631 6.265 ↓ 1.0 2,716 1

Hash Join (cost=349.83..575.62 rows=2,711 width=21) (actual time=3.915..6.265 rows=2,716 loops=1)

  • Hash Cond: (pt.id = prd.product_tmpl_id)
20. 0.733 0.733 ↓ 1.0 3,058 1

Seq Scan on product_template pt (cost=0.00..179.57 rows=3,057 width=8) (actual time=0.008..0.733 rows=3,058 loops=1)

21. 0.698 3.901 ↓ 1.0 2,716 1

Hash (cost=315.95..315.95 rows=2,711 width=21) (actual time=3.901..3.901 rows=2,716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
22. 1.198 3.203 ↓ 1.0 2,716 1

Hash Join (cost=215.78..315.95 rows=2,711 width=21) (actual time=1.649..3.203 rows=2,716 loops=1)

  • Hash Cond: (irch.product_id = prd.id)
23. 0.368 0.368 ↓ 1.0 2,716 1

Seq Scan on inno_rchmaterial irch (cost=0.00..56.11 rows=2,711 width=13) (actual time=0.004..0.368 rows=2,716 loops=1)

24. 0.636 1.637 ↓ 1.0 3,058 1

Hash (cost=177.57..177.57 rows=3,057 width=8) (actual time=1.637..1.637 rows=3,058 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 120kB
25. 1.001 1.001 ↓ 1.0 3,058 1

Seq Scan on product_product prd (cost=0.00..177.57 rows=3,057 width=8) (actual time=0.003..1.001 rows=3,058 loops=1)

26. 98.584 249.101 ↓ 1.0 457,492 1

Hash (cost=19,620.57..19,620.57 rows=454,657 width=8) (actual time=249.101..249.101 rows=457,492 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 17871kB
27. 150.517 150.517 ↓ 1.0 457,492 1

Seq Scan on sale_order so (cost=0.00..19,620.57 rows=454,657 width=8) (actual time=0.006..150.517 rows=457,492 loops=1)

28. 1.265 4.799 ↓ 1.0 3,058 1

Hash (cost=177.57..177.57 rows=3,057 width=8) (actual time=4.799..4.799 rows=3,058 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 111kB
29. 3.534 3.534 ↓ 1.0 3,058 1

Seq Scan on product_product prd (cost=0.00..177.57 rows=3,057 width=8) (actual time=0.038..3.534 rows=3,058 loops=1)