explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HAkm

Settings
# exclusive inclusive rows x rows loops node
1. 0.196 409,196.720 ↓ 3.3 75 1

Sort (cost=79,721.55..79,721.60 rows=23 width=752) (actual time=409,196.710..409,196.720 rows=75 loops=1)

  • Sort Key: sla_0.code
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=15737 read=61210 dirtied=123
2. 0.107 409,196.524 ↓ 3.3 75 1

Subquery Scan on sla_0 (cost=10.55..79,721.03 rows=23 width=752) (actual time=209.924..409,196.524 rows=75 loops=1)

  • Buffers: shared hit=15737 read=61210 dirtied=123
3. 8.569 409,196.417 ↓ 3.3 75 1

Nested Loop Left Join (cost=10.55..79,720.80 rows=23 width=950) (actual time=209.923..409,196.417 rows=75 loops=1)

  • Buffers: shared hit=15737 read=61210 dirtied=123
4. 0.091 409,160.672 ↓ 3.3 75 1

Nested Loop Left Join (cost=10.27..13,724.44 rows=23 width=947) (actual time=209.355..409,160.672 rows=75 loops=1)

  • Buffers: shared hit=15404 read=61164 dirtied=123
5. 0.110 409,160.281 ↓ 3.3 75 1

Nested Loop Left Join (cost=9.98..13,628.74 rows=23 width=944) (actual time=209.352..409,160.281 rows=75 loops=1)

  • Join Filter: (spod.tstamp = sc.tstamp)
  • Buffers: shared hit=15179 read=61164 dirtied=123
6. 0.068 408,940.121 ↓ 3.3 75 1

Nested Loop Left Join (cost=9.43..13,517.54 rows=23 width=937) (actual time=209.341..408,940.121 rows=75 loops=1)

  • Join Filter: ((sdel.item = sc.item) AND (sdel.tstamp = sc.tstamp))
  • Buffers: shared hit=14889 read=61154 dirtied=123
7. 0.173 408,939.453 ↓ 3.3 75 1

Nested Loop Left Join (cost=8.87..13,406.22 rows=23 width=914) (actual time=209.325..408,939.453 rows=75 loops=1)

  • Buffers: shared hit=14587 read=61154 dirtied=123
8. 0.136 408,937.780 ↓ 3.3 75 1

Nested Loop Left Join (cost=8.18..13,297.60 rows=23 width=899) (actual time=209.306..408,937.780 rows=75 loops=1)

  • Join Filter: ((scoll.item = sc.item) AND (scoll.tstamp = sc.tstamp))
  • Buffers: shared hit=14212 read=61154 dirtied=123
9. 0.184 408,895.344 ↓ 3.3 75 1

Nested Loop Left Join (cost=7.62..13,186.28 rows=23 width=837) (actual time=209.292..408,895.344 rows=75 loops=1)

  • Buffers: shared hit=13919 read=61147 dirtied=123
10. 0.169 408,347.585 ↓ 3.3 75 1

Nested Loop Left Join (cost=6.92..13,077.60 rows=23 width=808) (actual time=209.150..408,347.585 rows=75 loops=1)

  • Buffers: shared hit=13500 read=61097 dirtied=103
11. 1.950 405,283.891 ↓ 3.3 75 1

Nested Loop Left Join (cost=6.23..10,702.77 rows=23 width=742) (actual time=208.318..405,283.891 rows=75 loops=1)

  • Join Filter: (car.code = c.carrier)
  • Rows Removed by Join Filter: 21299
  • Buffers: shared hit=9743 read=60828 dirtied=22
12. 0.091 405,280.666 ↓ 3.3 75 1

Nested Loop Left Join (cost=6.23..10,596.24 rows=23 width=734) (actual time=208.175..405,280.666 rows=75 loops=1)

  • Buffers: shared hit=9738 read=60828 dirtied=22
13. 0.084 405,269.775 ↓ 3.3 75 1

Nested Loop Left Join (cost=5.80..10,493.66 rows=23 width=724) (actual time=208.160..405,269.775 rows=75 loops=1)

  • Buffers: shared hit=9439 read=60827 dirtied=22
14. 0.111 405,269.316 ↓ 3.3 75 1

Nested Loop Left Join (cost=5.36..10,391.08 rows=23 width=685) (actual time=208.130..405,269.316 rows=75 loops=1)

  • Buffers: shared hit=9139 read=60827 dirtied=22
15. 0.084 405,109.755 ↓ 3.3 75 1

Nested Loop Left Join (cost=4.93..10,288.50 rows=23 width=682) (actual time=62.151..405,109.755 rows=75 loops=1)

  • Buffers: shared hit=8843 read=60823 dirtied=22
16. 0.090 405,109.671 ↓ 3.3 75 1

Nested Loop Left Join (cost=4.50..10,188.94 rows=23 width=653) (actual time=62.150..405,109.671 rows=75 loops=1)

  • Buffers: shared hit=8843 read=60823 dirtied=22
17. 0.090 405,109.581 ↓ 3.3 75 1

Nested Loop Left Join (cost=4.07..10,089.63 rows=23 width=624) (actual time=62.149..405,109.581 rows=75 loops=1)

  • Buffers: shared hit=8843 read=60823 dirtied=22
18. 0.093 405,109.491 ↓ 3.3 75 1

Nested Loop Left Join (cost=3.50..9,987.30 rows=23 width=620) (actual time=62.148..405,109.491 rows=75 loops=1)

  • Buffers: shared hit=8843 read=60823 dirtied=22
19. 0.052 405,109.173 ↓ 3.3 75 1

Nested Loop Left Join (cost=3.22..9,888.11 rows=23 width=600) (actual time=62.140..405,109.173 rows=75 loops=1)

  • Buffers: shared hit=8617 read=60823 dirtied=22
20. 0.104 405,108.971 ↓ 3.3 75 1

Nested Loop Left Join (cost=2.93..9,788.92 rows=23 width=580) (actual time=62.134..405,108.971 rows=75 loops=1)

  • Buffers: shared hit=8467 read=60823 dirtied=22
21. 0.078 405,108.567 ↓ 3.3 75 1

Nested Loop Left Join (cost=2.52..9,644.19 rows=23 width=576) (actual time=62.121..405,108.567 rows=75 loops=1)

  • Buffers: shared hit=8235 read=60823 dirtied=22
22. 0.086 405,107.814 ↓ 3.3 75 1

Nested Loop Left Join (cost=1.96..9,502.15 rows=23 width=568) (actual time=62.106..405,107.814 rows=75 loops=1)

  • Buffers: shared hit=7935 read=60823 dirtied=22
23. 0.113 405,107.503 ↓ 3.3 75 1

Nested Loop Left Join (cost=1.68..9,415.19 rows=23 width=556) (actual time=62.097..405,107.503 rows=75 loops=1)

  • Buffers: shared hit=7710 read=60823 dirtied=22
24. 0.135 405,107.240 ↓ 3.3 75 1

Nested Loop Left Join (cost=1.27..9,312.96 rows=23 width=546) (actual time=62.095..405,107.240 rows=75 loops=1)

  • Buffers: shared hit=7458 read=60823 dirtied=22
25. 0.090 405,071.330 ↓ 3.3 75 1

Nested Loop (cost=0.85..9,210.72 rows=23 width=529) (actual time=38.058..405,071.330 rows=75 loops=1)

  • Buffers: shared hit=7160 read=60821 dirtied=22
26. 0.029 0.029 ↑ 1.0 1 1

Index Scan using accounts_pk on accounts a (cost=0.28..4.30 rows=1 width=29) (actual time=0.009..0.029 rows=1 loops=1)

  • Index Cond: (code = 424)
  • Buffers: shared hit=3
27. 405,071.211 405,071.211 ↓ 3.3 75 1

Index Scan using consignments_zz1 on consignments c (cost=0.57..9,206.19 rows=23 width=520) (actual time=38.046..405,071.211 rows=75 loops=1)

  • Index Cond: ((acc_code = 424) AND ((req_type)::text = 'PUDO'::text))
  • Filter: (((req_status)::text <> 'CANC'::text) AND ((req_status)::text <> 'HIDE'::text) AND (((ready_tstamp).tstamp + (ready_tstamp).tzone) >= date(((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '30 days'::interval))) AND (((ready_tstamp).tstamp + (ready_tstamp).tzone) <= date((((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:00:00'::interval) + '1 day'::interval))))
  • Rows Removed by Filter: 72128
  • Buffers: shared hit=7157 read=60821 dirtied=22
28. 35.775 35.775 ↑ 1.0 1 75

Index Scan using agents_pk on agents ag (cost=0.42..4.44 rows=1 width=25) (actual time=0.476..0.477 rows=1 loops=75)

  • Index Cond: (code = c.agent_code)
  • Buffers: shared hit=298 read=2
29. 0.150 0.150 ↑ 1.0 1 75

Index Scan using agents_pk on agents ag2 (cost=0.42..4.44 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=75)

  • Index Cond: (code = c.coll_agent)
  • Buffers: shared hit=252
30. 0.225 0.225 ↑ 1.0 1 75

Index Scan using price_components_pk on price_components pc (cost=0.28..3.77 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=75)

  • Index Cond: (code = c.network_service)
  • Buffers: shared hit=225
31. 0.675 0.675 ↓ 0.0 0 75

Index Scan using request_items_cons_code_idx on request_items ri (cost=0.56..5.65 rows=53 width=16) (actual time=0.009..0.009 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=300
32. 0.300 0.300 ↓ 0.0 0 75

Index Scan using request_reserves_i1 on request_reserves rr (cost=0.41..6.27 rows=2 width=12) (actual time=0.004..0.004 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=232
33. 0.150 0.150 ↓ 0.0 0 75

Index Scan using depots_pk on depots cd (cost=0.29..4.30 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=75)

  • Index Cond: (code = c.col_dep)
  • Buffers: shared hit=150
34. 0.225 0.225 ↑ 1.0 1 75

Index Scan using depots_pk on depots dd (cost=0.29..4.30 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=75)

  • Index Cond: (code = c.del_dep)
  • Buffers: shared hit=226
35. 0.000 0.000 ↓ 0.0 0 75

Index Scan using items_pk on items i (cost=0.56..4.44 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=75)

  • Index Cond: (ri.item_code = code)
36. 0.000 0.000 ↓ 0.0 0 75

Index Scan using skus_pk on skus s (cost=0.43..4.31 rows=1 width=37) (actual time=0.000..0.000 rows=0 loops=75)

  • Index Cond: (code = i.sku_code)
37. 0.000 0.000 ↓ 0.0 0 75

Index Scan using skus_pk on skus s2 (cost=0.43..4.32 rows=1 width=37) (actual time=0.000..0.000 rows=0 loops=75)

  • Index Cond: (code = rr.sku_code)
38. 159.450 159.450 ↑ 1.0 1 75

Index Scan using addresses_pk on addresses ca (cost=0.43..4.45 rows=1 width=11) (actual time=2.126..2.126 rows=1 loops=75)

  • Index Cond: (c.col_addr = code)
  • Buffers: shared hit=296 read=4
39. 0.375 0.375 ↑ 1.0 1 75

Index Scan using addresses_pk on addresses da (cost=0.43..4.45 rows=1 width=47) (actual time=0.005..0.005 rows=1 loops=75)

  • Index Cond: (c.del_addr = code)
  • Buffers: shared hit=300
40. 10.800 10.800 ↑ 1.0 1 75

Index Scan using contacts_pk on contacts dco (cost=0.43..4.45 rows=1 width=18) (actual time=0.144..0.144 rows=1 loops=75)

  • Index Cond: (c.del_cont = code)
  • Buffers: shared hit=299 read=1
41. 1.203 1.275 ↑ 1.0 284 75

Materialize (cost=0.00..9.26 rows=284 width=16) (actual time=0.001..0.017 rows=284 loops=75)

  • Buffers: shared hit=5
42. 0.072 0.072 ↑ 1.0 284 1

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

  • Buffers: shared hit=5
43. 3,027.675 3,063.525 ↑ 8.0 1 75

Index Scan using scans_pk on scans sc (cost=0.70..103.17 rows=8 width=74) (actual time=27.053..40.847 rows=1 loops=75)

  • Index Cond: (cons_code = c.code)
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=3757 read=269 dirtied=81
44.          

SubPlan (forIndex Scan)

45. 0.478 35.850 ↑ 1.0 1 478

Limit (cost=5.06..5.06 rows=1 width=51) (actual time=0.075..0.075 rows=1 loops=478)

  • Buffers: shared hit=3140 read=1 dirtied=23
46. 21.510 35.372 ↑ 16.0 1 478

Sort (cost=5.06..5.10 rows=16 width=51) (actual time=0.074..0.074 rows=1 loops=478)

  • Sort Key: scans.tstamp DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=3140 read=1 dirtied=23
47. 13.862 13.862 ↑ 2.3 7 478

Index Only Scan using scans_pk on scans (cost=0.70..4.98 rows=16 width=51) (actual time=0.023..0.029 rows=7 loops=478)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 597
  • Buffers: shared hit=3129 read=1 dirtied=23
48. 547.575 547.575 ↑ 1.0 1 75

Index Scan using scan_checks_pk on scan_checks schk (cost=0.69..4.71 rows=1 width=88) (actual time=7.300..7.301 rows=1 loops=75)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Buffers: shared hit=419 read=50 dirtied=20
49. 42.300 42.300 ↓ 0.0 0 75

Index Scan using scan_collects_pk on scan_collects scoll (cost=0.56..4.58 rows=1 width=121) (actual time=0.564..0.564 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=293 read=7
50. 1.500 1.500 ↓ 0.0 0 75

Index Scan using scan_containers_pk on scan_containers scont (cost=0.69..4.71 rows=1 width=74) (actual time=0.020..0.020 rows=0 loops=75)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Buffers: shared hit=375
51. 0.600 0.600 ↓ 0.0 0 75

Index Scan using scan_delivers_pk on scan_delivers sdel (cost=0.56..4.58 rows=1 width=84) (actual time=0.008..0.008 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=302
52. 220.050 220.050 ↓ 0.0 0 75

Index Scan using scan_pods_pk on scan_pods spod (cost=0.56..4.58 rows=1 width=64) (actual time=2.934..2.934 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=290 read=10
53. 0.300 0.300 ↑ 1.0 1 75

Index Scan using depots_pk on depots scd (cost=0.29..4.15 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=75)

  • Index Cond: (code = sc.dep_code)
  • Buffers: shared hit=225
54. 0.000 0.000 ↓ 0.0 0 75

Index Scan using customer_sites_pk on customer_sites sccs (cost=0.29..4.14 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=75)

  • Index Cond: (code = sc.cust_site)
55.          

SubPlan (forNested Loop Left Join)

56. 0.008 26.876 ↑ 1.0 1 1

Aggregate (cost=2,856.84..2,856.86 rows=1 width=0) (actual time=26.876..26.876 rows=1 loops=1)

  • Buffers: shared hit=25 read=46
57. 26.868 26.868 ↑ 474.0 2 1

Seq Scan on operational_days (cost=0.00..2,854.47 rows=948 width=0) (actual time=22.306..26.868 rows=2 loops=1)

  • Filter: (((type)::text = 'NORMAL'::text) AND (coldate > c.created) AND (coldate <= c.completed_tstamp))
  • Rows Removed by Filter: 5476
  • Buffers: shared hit=25 read=46
58. 0.075 0.300 ↓ 0.0 0 75

Limit (cost=0.56..4.58 rows=1 width=63) (actual time=0.004..0.004 rows=0 loops=75)

  • Buffers: shared hit=300
59. 0.225 0.225 ↓ 0.0 0 75

Index Scan Backward using scan_pods_pk on scan_pods sp (cost=0.56..4.58 rows=1 width=63) (actual time=0.003..0.003 rows=0 loops=75)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=300
Planning time : 18.125 ms
Execution time : 409,198.373 ms