explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2RM

Settings
# exclusive inclusive rows x rows loops node
1. 8.096 607,468.036 ↑ 49.3 2,372 1

Sort (cost=358,285,427.67..358,285,720.09 rows=116,969 width=896) (actual time=607,467.887..607,468.036 rows=2,372 loops=1)

  • Sort Key: sla_0.account
  • Sort Method: quicksort Memory: 1658kB
  • Buffers: shared hit=399496 read=5573178 dirtied=57661, temp read=118986 written=149194
2. 2.618 607,459.940 ↑ 49.3 2,372 1

Subquery Scan on sla_0 (cost=2,235,147.73..358,242,738.85 rows=116,969 width=896) (actual time=327,933.380..607,459.940 rows=2,372 loops=1)

  • Buffers: shared hit=399493 read=5573178 dirtied=57661, temp read=118986 written=149194
3. 194.209 607,457.322 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,147.73..358,241,569.16 rows=116,969 width=740) (actual time=327,933.374..607,457.322 rows=2,372 loops=1)

  • Join Filter: (spod.tstamp = sc.tstamp)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=399493 read=5573178 dirtied=57661, temp read=118986 written=149194
4. 2.484 600,718.765 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,147.17..23,307,253.35 rows=116,969 width=740) (actual time=327,833.491..600,718.765 rows=2,372 loops=1)

  • Join Filter: ((sdel.item = sc.item) AND (sdel.tstamp = sc.tstamp))
  • Rows Removed by Join Filter: 25
  • Buffers: shared hit=390491 read=5572606 dirtied=57661, temp read=118986 written=149194
5. 3.719 595,196.637 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,146.61..22,979,635.39 rows=116,969 width=742) (actual time=327,760.515..595,196.637 rows=2,372 loops=1)

  • Buffers: shared hit=381122 read=5572224 dirtied=57570, temp read=118986 written=149194
6. 14.814 595,093.294 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,145.92..22,665,379.29 rows=116,969 width=742) (actual time=327,760.474..595,093.294 rows=2,372 loops=1)

  • Join Filter: ((scoll.item = sc.item) AND (scoll.tstamp = sc.tstamp))
  • Rows Removed by Join Filter: 2243
  • Buffers: shared hit=367773 read=5572222 dirtied=57153, temp read=118986 written=149194
7. 3.529 590,600.144 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,145.36..22,338,737.33 rows=116,969 width=742) (actual time=327,741.654..590,600.144 rows=2,372 loops=1)

  • Buffers: shared hit=356422 read=5571859 dirtied=56804, temp read=118986 written=149194
8. 3.356 580,748.071 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,235,144.67..22,024,020.81 rows=116,969 width=742) (actual time=327,723.902..580,748.071 rows=2,372 loops=1)

  • Buffers: shared hit=344506 read=5571088 dirtied=56433, temp read=118986 written=149194
9. 1.636 572,786.655 ↑ 49.3 2,372 1

Hash Left Join (cost=2,235,143.97..9,987,298.56 rows=116,969 width=691) (actual time=327,696.314..572,786.655 rows=2,372 loops=1)

  • Hash Cond: (c.carrier = car.code)
  • Buffers: shared hit=274624 read=5570534 dirtied=54720, temp read=118986 written=149194
10. 849.363 572,784.897 ↑ 49.3 2,372 1

Hash Left Join (cost=2,235,132.58..9,986,494.47 rows=116,969 width=683) (actual time=327,696.146..572,784.897 rows=2,372 loops=1)

  • Hash Cond: (i.sku_code = s.code)
  • Buffers: shared hit=274619 read=5570534 dirtied=54720, temp read=118986 written=149194
11. 4.403 570,283.989 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=2,000,132.96..9,695,875.89 rows=116,969 width=654) (actual time=291,463.350..570,283.989 rows=2,372 loops=1)

  • Buffers: shared hit=172127 read=5570534 dirtied=54720, temp read=88639 written=118877
12. 3.680 568,322.686 ↑ 49.3 2,372 1

Hash Left Join (cost=2,000,132.40..9,170,464.28 rows=116,969 width=635) (actual time=291,455.791..568,322.686 rows=2,372 loops=1)

  • Hash Cond: (rr.sku_code = s2.code)
  • Buffers: shared hit=167781 read=5570398 dirtied=54687, temp read=88639 written=118877
13. 1,748.472 562,614.859 ↑ 49.3 2,372 1

Hash Left Join (cost=1,765,132.77..8,881,217.70 rows=116,969 width=606) (actual time=285,749.738..562,614.859 rows=2,372 loops=1)

  • Hash Cond: (c.del_addr = da.code)
  • Buffers: shared hit=157254 read=5478433 dirtied=53383, temp read=88639 written=88603
14. 1.634 555,416.415 ↑ 49.3 2,372 1

Hash Left Join (cost=1,502,320.43..8,565,970.85 rows=116,969 width=603) (actual time=280,296.104..555,416.415 rows=2,372 loops=1)

  • Hash Cond: (c.del_dep = dd.code)
  • Buffers: shared hit=154523 read=5397285 dirtied=51980, temp read=63837 written=63831
15. 3.784 555,410.649 ↑ 49.3 2,372 1

Hash Left Join (cost=1,501,486.18..8,563,528.46 rows=116,969 width=583) (actual time=280,291.880..555,410.649 rows=2,372 loops=1)

  • Hash Cond: (c.col_dep = cd.code)
  • Buffers: shared hit=153976 read=5397285 dirtied=51980, temp read=63837 written=63831
16. 7,681.369 555,400.607 ↑ 49.3 2,372 1

Hash Right Join (cost=1,500,651.92..8,561,093.48 rows=116,969 width=563) (actual time=280,285.485..555,400.607 rows=2,372 loops=1)

  • Hash Cond: (or18.cons_code = c.code)
  • Buffers: shared hit=153429 read=5397285 dirtied=51977, temp read=63837 written=63831
17. 443,652.810 547,476.583 ↑ 1.0 16,766,126 1

Bitmap Heap Scan on cons_refs or18 (cost=300,380.13..7,123,240.99 rows=16,945,749 width=16) (actual time=103,957.073..547,476.583 rows=16,766,126 loops=1)

  • Recheck Cond: ((ref_type)::text = 'OR18'::text)
  • Rows Removed by Index Recheck: 388120031
  • Heap Blocks: exact=333850 lossy=5076771
  • Buffers: shared hit=99159 read=5397285 dirtied=51603
18. 103,823.773 103,823.773 ↑ 1.0 16,766,157 1

Bitmap Index Scan on cons_refs_ref_type_ref_text_in (cost=0.00..296,143.69 rows=16,945,749 width=0) (actual time=103,823.773..103,823.773 rows=16,766,157 loops=1)

  • Index Cond: ((ref_type)::text = 'OR18'::text)
  • Buffers: shared hit=70465 read=15358
19. 2.452 242.655 ↑ 49.3 2,372 1

Hash (cost=1,190,470.68..1,190,470.68 rows=116,969 width=555) (actual time=242.655..242.655 rows=2,372 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 712kB
  • Buffers: shared hit=54267 dirtied=374, temp written=69
20. 1.074 240.203 ↑ 49.3 2,372 1

Hash Left Join (cost=7,062.95..1,190,470.68 rows=116,969 width=555) (actual time=77.692..240.203 rows=2,372 loops=1)

  • Hash Cond: (c.code = rr.cons_code)
  • Buffers: shared hit=54267 dirtied=374
21. 0.000 232.521 ↑ 49.3 2,372 1

Nested Loop Left Join (cost=6,358.43..1,171,928.20 rows=116,969 width=551) (actual time=71.008..232.521 rows=2,372 loops=1)

  • Buffers: shared hit=53905 dirtied=167
22. 0.751 213.678 ↑ 49.3 2,372 1

Hash Left Join (cost=6,357.86..461,002.61 rows=116,969 width=543) (actual time=70.966..213.678 rows=2,372 loops=1)

  • Hash Cond: (c.network_service = pc.code)
  • Buffers: shared hit=43540 dirtied=167
23. 0.763 212.665 ↑ 49.3 2,372 1

Hash Left Join (cost=6,327.97..459,364.39 rows=116,969 width=531) (actual time=70.692..212.665 rows=2,372 loops=1)

  • Hash Cond: (c.acc_code = a.code)
  • Buffers: shared hit=43527 dirtied=167
24. 0.725 210.611 ↑ 49.3 2,372 1

Hash Left Join (cost=6,191.70..457,619.80 rows=116,969 width=510) (actual time=69.373..210.611 rows=2,372 loops=1)

  • Hash Cond: (c.network_service = sv.code)
  • Buffers: shared hit=43439 dirtied=167
25. 4.452 209.216 ↑ 49.3 2,372 1

Hash Left Join (cost=6,135.35..455,955.12 rows=116,969 width=494) (actual time=68.685..209.216 rows=2,372 loops=1)

  • Hash Cond: (c.agent_code = ag.code)
  • Buffers: shared hit=43421 dirtied=167
26. 139.479 139.479 ↑ 49.3 2,372 1

Index Scan using consignments_ready_tstamp_loc_in on consignments c (cost=0.59..448,838.37 rows=116,969 width=516) (actual time=2.742..139.479 rows=2,372 loops=1)

  • Index Cond: ((((ready_tstamp).tstamp + (ready_tstamp).tzone) >= date(((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '1 day'::interval))) AND (((ready_tstamp).tstamp + (ready_tstamp).tzone) <= date((((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '1 day'::interval) + '1 day'::interval))))
  • Filter: (((req_status)::text <> 'CANC'::text) AND ((req_status)::text <> 'HIDE'::text) AND ((req_type)::text = 'RTRN'::text))
  • Rows Removed by Filter: 26399
  • Buffers: shared hit=40115 dirtied=166
27. 21.270 65.285 ↓ 1.0 125,931 1

Hash (cost=4,561.56..4,561.56 rows=125,856 width=10) (actual time=65.285..65.285 rows=125,931 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6520kB
  • Buffers: shared hit=3303 dirtied=1
28. 44.015 44.015 ↓ 1.0 125,931 1

Seq Scan on agents ag (cost=0.00..4,561.56 rows=125,856 width=10) (actual time=0.015..44.015 rows=125,931 loops=1)

  • Buffers: shared hit=3303 dirtied=1
29. 0.087 0.670 ↑ 1.0 592 1

Hash (cost=48.96..48.96 rows=592 width=20) (actual time=0.670..0.670 rows=592 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=18
30. 0.149 0.583 ↑ 1.0 592 1

Hash Left Join (cost=29.90..48.96 rows=592 width=20) (actual time=0.385..0.583 rows=592 loops=1)

  • Hash Cond: (sv.who_component = pc2.code)
  • Buffers: shared hit=18
31. 0.070 0.070 ↑ 1.0 592 1

Seq Scan on services sv (cost=0.00..10.92 rows=592 width=8) (actual time=0.008..0.070 rows=592 loops=1)

  • Buffers: shared hit=5
32. 0.152 0.364 ↑ 1.0 751 1

Hash (cost=20.51..20.51 rows=751 width=20) (actual time=0.364..0.364 rows=751 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=13
33. 0.212 0.212 ↑ 1.0 751 1

Seq Scan on price_components pc2 (cost=0.00..20.51 rows=751 width=20) (actual time=0.006..0.212 rows=751 loops=1)

  • Buffers: shared hit=13
34. 0.451 1.291 ↓ 1.0 2,150 1

Hash (cost=109.45..109.45 rows=2,145 width=29) (actual time=1.291..1.291 rows=2,150 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 166kB
  • Buffers: shared hit=88
35. 0.840 0.840 ↓ 1.0 2,150 1

Seq Scan on accounts a (cost=0.00..109.45 rows=2,145 width=29) (actual time=0.007..0.840 rows=2,150 loops=1)

  • Buffers: shared hit=88
36. 0.124 0.262 ↑ 1.0 751 1

Hash (cost=20.51..20.51 rows=751 width=20) (actual time=0.262..0.262 rows=751 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=13
37. 0.138 0.138 ↑ 1.0 751 1

Seq Scan on price_components pc (cost=0.00..20.51 rows=751 width=20) (actual time=0.002..0.138 rows=751 loops=1)

  • Buffers: shared hit=13
38. 18.976 18.976 ↓ 0.0 0 2,372

Index Scan using request_items_cons_code_idx on request_items ri (cost=0.56..5.55 rows=53 width=16) (actual time=0.007..0.008 rows=0 loops=2,372)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=10365
39. 1.916 6.608 ↑ 1.2 13,050 1

Hash (cost=514.23..514.23 rows=15,223 width=12) (actual time=6.608..6.608 rows=13,050 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 689kB
  • Buffers: shared hit=362 dirtied=207
40. 4.692 4.692 ↑ 1.2 13,050 1

Seq Scan on request_reserves rr (cost=0.00..514.23 rows=15,223 width=12) (actual time=0.014..4.692 rows=13,050 loops=1)

  • Buffers: shared hit=362 dirtied=207
41. 2.622 6.258 ↓ 1.0 12,781 1

Hash (cost=674.67..674.67 rows=12,767 width=28) (actual time=6.258..6.258 rows=12,781 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 902kB
  • Buffers: shared hit=547 dirtied=3
42. 3.636 3.636 ↓ 1.0 12,781 1

Seq Scan on depots cd (cost=0.00..674.67 rows=12,767 width=28) (actual time=0.010..3.636 rows=12,781 loops=1)

  • Buffers: shared hit=547 dirtied=3
43. 2.499 4.132 ↓ 1.0 12,781 1

Hash (cost=674.67..674.67 rows=12,767 width=28) (actual time=4.132..4.132 rows=12,781 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 902kB
  • Buffers: shared hit=547
44. 1.633 1.633 ↓ 1.0 12,781 1

Seq Scan on depots dd (cost=0.00..674.67 rows=12,767 width=28) (actual time=0.005..1.633 rows=12,781 loops=1)

  • Buffers: shared hit=547
45. 1,219.270 5,449.972 ↑ 1.0 6,527,632 1

Hash (cost=149,224.04..149,224.04 rows=6,534,504 width=11) (actual time=5,449.972..5,449.972 rows=6,527,632 loops=1)

  • Buckets: 1048576 Batches: 16 Memory Usage: 26156kB
  • Buffers: shared hit=2731 read=81148 dirtied=1403, temp written=24714
46. 4,230.702 4,230.702 ↑ 1.0 6,527,632 1

Seq Scan on addresses da (cost=0.00..149,224.04 rows=6,534,504 width=11) (actual time=6.020..4,230.702 rows=6,527,632 loops=1)

  • Buffers: shared hit=2731 read=81148 dirtied=1403
47. 900.338 5,704.147 ↓ 1.0 4,408,750 1

Hash (cost=146,205.61..146,205.61 rows=4,371,361 width=37) (actual time=5,704.147..5,704.147 rows=4,408,750 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 23539kB
  • Buffers: shared hit=10527 read=91965 dirtied=1304, temp written=30259
48. 4,803.809 4,803.809 ↓ 1.0 4,408,750 1

Seq Scan on skus s2 (cost=0.00..146,205.61 rows=4,371,361 width=37) (actual time=0.009..4,803.809 rows=4,408,750 loops=1)

  • Buffers: shared hit=10527 read=91965 dirtied=1304
49. 1,956.900 1,956.900 ↓ 0.0 0 2,372

Index Scan using items_pk on items i (cost=0.56..4.48 rows=1 width=27) (actual time=0.819..0.825 rows=0 loops=2,372)

  • Index Cond: (ri.item_code = code)
  • Buffers: shared hit=4346 read=136 dirtied=33
50. 886.218 1,651.545 ↓ 1.0 4,408,750 1

Hash (cost=146,205.61..146,205.61 rows=4,371,361 width=37) (actual time=1,651.545..1,651.545 rows=4,408,750 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 23539kB
  • Buffers: shared hit=102492, temp written=30258
51. 765.327 765.327 ↓ 1.0 4,408,750 1

Seq Scan on skus s (cost=0.00..146,205.61 rows=4,371,361 width=37) (actual time=0.006..765.327 rows=4,408,750 loops=1)

  • Buffers: shared hit=102492
52. 0.057 0.122 ↑ 1.0 284 1

Hash (cost=7.84..7.84 rows=284 width=16) (actual time=0.122..0.122 rows=284 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=5
53. 0.065 0.065 ↑ 1.0 284 1

Seq Scan on carriers car (cost=0.00..7.84 rows=284 width=16) (actual time=0.013..0.065 rows=284 loops=1)

  • Buffers: shared hit=5
54. 7,561.438 7,958.060 ↑ 8.0 1 2,372

Index Scan using scans_pk on scans sc (cost=0.70..102.83 rows=8 width=67) (actual time=3.262..3.355 rows=1 loops=2,372)

  • Index Cond: (cons_code = c.code)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=69882 read=554 dirtied=1713
55.          

SubPlan (forIndex Scan)

56. 6.502 396.622 ↑ 1.0 1 6,502

Limit (cost=5.06..5.06 rows=1 width=51) (actual time=0.061..0.061 rows=1 loops=6,502)

  • Buffers: shared hit=51969 read=8 dirtied=1251
57. 91.028 390.120 ↑ 16.0 1 6,502

Sort (cost=5.06..5.10 rows=16 width=51) (actual time=0.060..0.060 rows=1 loops=6,502)

  • Sort Key: scans.tstamp DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=51969 read=8 dirtied=1251
58. 299.092 299.092 ↑ 5.3 3 6,502

Index Only Scan using scans_pk on scans (cost=0.70..4.98 rows=16 width=51) (actual time=0.003..0.046 rows=3 loops=6,502)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 19330
  • Buffers: shared hit=51949 read=8 dirtied=1251
59. 9,848.544 9,848.544 ↓ 0.0 0 2,372

Index Only Scan using scan_checks_pk on scan_checks schk (cost=0.69..2.68 rows=1 width=59) (actual time=4.139..4.152 rows=0 loops=2,372)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Heap Fetches: 726
  • Buffers: shared hit=11916 read=771 dirtied=371
60. 4,478.336 4,478.336 ↑ 1.0 1 2,372

Index Only Scan using scan_collects_pk on scan_collects scoll (cost=0.56..2.53 rows=1 width=59) (actual time=1.873..1.888 rows=1 loops=2,372)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 2180
  • Buffers: shared hit=11351 read=363 dirtied=349
61. 99.624 99.624 ↑ 1.0 1 2,372

Index Only Scan using scan_containers_pk on scan_containers scont (cost=0.69..2.68 rows=1 width=59) (actual time=0.042..0.042 rows=1 loops=2,372)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Heap Fetches: 1453
  • Buffers: shared hit=13349 read=2 dirtied=417
62. 5,519.644 5,519.644 ↓ 0.0 0 2,372

Index Only Scan using scan_delivers_pk on scan_delivers sdel (cost=0.56..2.54 rows=1 width=59) (actual time=2.327..2.327 rows=0 loops=2,372)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 243
  • Buffers: shared hit=9369 read=382 dirtied=91
63. 6,544.348 6,544.348 ↓ 0.0 0 2,372

Index Only Scan using scan_pods_pk on scan_pods spod (cost=0.56..2.53 rows=1 width=57) (actual time=2.759..2.759 rows=0 loops=2,372)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 1
  • Buffers: shared hit=8949 read=572
64.          

SubPlan (forNested Loop Left Join)

65. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2,856.84..2,856.86 rows=1 width=0) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Seq Scan on operational_days (cost=0.00..2,854.47 rows=948 width=0) (never executed)

  • Filter: (((type)::text = 'NORMAL'::text) AND (coldate > c.created) AND (coldate <= c.completed_tstamp))
Planning time : 29.268 ms
Execution time : 607,475.884 ms