explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IfC

Settings
# exclusive inclusive rows x rows loops node
1. 0.374 7,228.186 ↑ 3.7 721 1

Sort (cost=8,091,955.22..8,091,961.92 rows=2,680 width=129) (actual time=7,228.155..7,228.186 rows=721 loops=1)

  • Sort Key: sla_0.accountname
  • Sort Method: quicksort Memory: 126kB
  • Buffers: shared hit=62663 read=1094 dirtied=819
2. 0.466 7,227.812 ↑ 3.7 721 1

Subquery Scan on sla_0 (cost=75,770.45..8,091,802.62 rows=2,680 width=129) (actual time=7,119.769..7,227.812 rows=721 loops=1)

  • Buffers: shared hit=62663 read=1094 dirtied=819
3. 29.594 7,227.346 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,770.45..8,091,775.82 rows=2,680 width=602) (actual time=7,119.765..7,227.346 rows=721 loops=1)

  • Join Filter: (spod.tstamp = sc.tstamp)
  • Buffers: shared hit=62663 read=1094 dirtied=819
4. 0.581 7,197.031 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,769.90..417,722.02 rows=2,680 width=610) (actual time=7,118.896..7,197.031 rows=721 loops=1)

  • Join Filter: ((sdel.item = sc.item) AND (sdel.tstamp = sc.tstamp))
  • Buffers: shared hit=59724 read=1094 dirtied=819
5. 0.455 7,195.729 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,769.34..410,144.82 rows=2,680 width=612) (actual time=7,118.885..7,195.729 rows=721 loops=1)

  • Buffers: shared hit=56840 read=1094 dirtied=819
6. 0.683 7,195.274 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,768.65..402,840.52 rows=2,680 width=612) (actual time=7,118.879..7,195.274 rows=721 loops=1)

  • Join Filter: ((scoll.item = sc.item) AND (scoll.tstamp = sc.tstamp))
  • Buffers: shared hit=56840 read=1094 dirtied=819
7. 0.482 7,193.870 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,768.09..395,267.32 rows=2,680 width=612) (actual time=7,118.854..7,193.870 rows=721 loops=1)

  • Buffers: shared hit=53564 read=1094 dirtied=819
8. 0.431 7,193.388 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,767.39..387,958.32 rows=2,680 width=612) (actual time=7,118.849..7,193.388 rows=721 loops=1)

  • Buffers: shared hit=53564 read=1094 dirtied=819
9. 0.387 7,191.515 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,766.70..111,429.80 rows=2,680 width=561) (actual time=7,118.831..7,191.515 rows=721 loops=1)

  • Buffers: shared hit=49959 read=1094 dirtied=819
10. 0.694 7,191.128 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,766.27..99,834.44 rows=2,680 width=532) (actual time=7,118.828..7,191.128 rows=721 loops=1)

  • Buffers: shared hit=49959 read=1094 dirtied=819
11. 0.498 7,123.381 ↑ 3.7 721 1

Nested Loop Left Join (cost=75,765.83..88,269.23 rows=2,680 width=503) (actual time=7,118.747..7,123.381 rows=721 loops=1)

  • Buffers: shared hit=47077 read=1092 dirtied=817
12. 0.412 7,119.278 ↑ 3.7 721 1

Hash Left Join (cost=75,765.27..76,351.35 rows=2,680 width=495) (actual time=7,118.717..7,119.278 rows=721 loops=1)

  • Hash Cond: (c.del_dep = dd.code)
  • Buffers: shared hit=43404 read=1092 dirtied=814
13. 1.242 7,113.272 ↑ 3.7 721 1

Hash Right Join (cost=74,931.01..75,480.24 rows=2,680 width=492) (actual time=7,113.040..7,113.272 rows=721 loops=1)

  • Hash Cond: (rr.cons_code = c.code)
  • Buffers: shared hit=42854 read=1092 dirtied=813
14. 3.109 3.109 ↑ 1.0 13,528 1

Seq Scan on request_reserves rr (cost=0.00..498.16 rows=13,616 width=12) (actual time=0.011..3.109 rows=13,528 loops=1)

  • Buffers: shared hit=362 dirtied=197
15. 0.250 7,108.921 ↑ 3.7 721 1

Hash (cost=74,897.51..74,897.51 rows=2,680 width=488) (actual time=7,108.921..7,108.921 rows=721 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 175kB
  • Buffers: shared hit=42492 read=1092 dirtied=616
16. 0.066 7,108.671 ↑ 3.7 721 1

Nested Loop Left Join (cost=52,036.21..74,897.51 rows=2,680 width=488) (actual time=7,101.609..7,108.671 rows=721 loops=1)

  • Buffers: shared hit=42492 read=1092 dirtied=616
17. 0.247 7,106.442 ↑ 3.7 721 1

Nested Loop (cost=52,035.64..58,404.43 rows=2,680 width=480) (actual time=7,101.582..7,106.442 rows=721 loops=1)

  • Buffers: shared hit=38882 read=1092 dirtied=549
18. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: (code = 1745)
  • Buffers: shared hit=3
19. 17.551 7,106.186 ↑ 3.7 721 1

Bitmap Heap Scan on consignments c (cost=52,035.36..58,373.34 rows=2,680 width=510) (actual time=7,101.565..7,106.186 rows=721 loops=1)

  • Recheck Cond: ((((completed_tstamp).tstamp + (completed_tstamp).tzone) >= date(((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:00:00'::interval))) AND (((completed_tstamp).tstamp + (completed_tstamp).tzone) <= date((((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:00:00'::interval) + '1 day'::interval))) AND (acc_code = 1745) AND ((req_type)::text = 'REPL'::text))
  • Rows Removed by Index Recheck: 2434
  • Filter: (((req_status)::text <> 'CANC'::text) AND ((req_status)::text <> 'HIDE'::text))
  • Heap Blocks: exact=1802
  • Buffers: shared hit=38879 read=1092 dirtied=549
20. 8.125 7,088.635 ↓ 0.0 0 1

BitmapAnd (cost=52,035.36..52,035.36 rows=3,122 width=0) (actual time=7,088.635..7,088.635 rows=0 loops=1)

  • Buffers: shared hit=37077 read=1092
21. 5.141 5.141 ↑ 11.3 41,982 1

Bitmap Index Scan on consignments_completed_tstamp_loc_in (cost=0.00..15,641.70 rows=473,111 width=0) (actual time=5.141..5.141 rows=41,982 loops=1)

  • Index Cond: ((((completed_tstamp).tstamp + (completed_tstamp).tzone) >= date(((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:00:00'::interval))) AND (((completed_tstamp).tstamp + (completed_tstamp).tzone) <= date((((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:00:00'::interval) + '1 day'::interval))))
  • Buffers: shared hit=174
22. 7,075.369 7,075.369 ↓ 4.2 2,643,331 1

Bitmap Index Scan on consignments_zz1 (cost=0.00..36,392.08 rows=624,351 width=0) (actual time=7,075.369..7,075.369 rows=2,643,331 loops=1)

  • Index Cond: ((acc_code = 1745) AND ((req_type)::text = 'REPL'::text))
  • Buffers: shared hit=36903 read=1092
23. 2.163 2.163 ↑ 53.0 1 721

Index Scan using request_items_cons_code_idx on request_items ri (cost=0.56..5.62 rows=53 width=16) (actual time=0.003..0.003 rows=1 loops=721)

  • Index Cond: (cons_code = c.code)
  • Buffers: shared hit=3610 dirtied=67
24. 1.895 5.594 ↓ 1.0 12,778 1

Hash (cost=674.67..674.67 rows=12,767 width=11) (actual time=5.594..5.594 rows=12,778 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 682kB
  • Buffers: shared hit=547 dirtied=1
25. 3.699 3.699 ↓ 1.0 12,778 1

Seq Scan on depots dd (cost=0.00..674.67 rows=12,767 width=11) (actual time=0.006..3.699 rows=12,778 loops=1)

  • Buffers: shared hit=547 dirtied=1
26. 3.605 3.605 ↑ 1.0 1 721

Index Scan using items_pk on items i (cost=0.56..4.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=721)

  • Index Cond: (ri.item_code = code)
  • Buffers: shared hit=3673 dirtied=3
27. 67.053 67.053 ↑ 1.0 1 721

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

  • Index Cond: (code = i.sku_code)
  • Buffers: shared hit=2882 read=2 dirtied=2
28. 0.000 0.000 ↓ 0.0 0 721

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=721)

  • Index Cond: (code = rr.sku_code)
29. 1.442 1.442 ↓ 0.0 0 721

Index Scan using scans_pk on scans sc (cost=0.70..103.10 rows=8 width=67) (actual time=0.002..0.002 rows=0 loops=721)

  • Index Cond: (cons_code = c.code)
  • Filter: (SubPlan 2)
  • Buffers: shared hit=3605
30.          

SubPlan (forIndex Scan)

31. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.06..5.06 rows=1 width=51) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.06..5.10 rows=16 width=51) (never executed)

  • Sort Key: scans.tstamp DESC
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using scans_pk on scans (cost=0.70..4.98 rows=16 width=51) (never executed)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0 721

Index Only Scan using scan_checks_pk on scan_checks schk (cost=0.69..2.72 rows=1 width=59) (actual time=0.000..0.000 rows=0 loops=721)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Heap Fetches: 0
35. 0.721 0.721 ↓ 0.0 0 721

Index Only Scan using scan_collects_pk on scan_collects scoll (cost=0.56..2.56 rows=1 width=59) (actual time=0.001..0.001 rows=0 loops=721)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 0
  • Buffers: shared hit=3276
36. 0.000 0.000 ↓ 0.0 0 721

Index Only Scan using scan_containers_pk on scan_containers scont (cost=0.69..2.72 rows=1 width=59) (actual time=0.000..0.000 rows=0 loops=721)

  • Index Cond: ((cons_code = c.code) AND (item = sc.item) AND (tstamp = sc.tstamp))
  • Heap Fetches: 0
37. 0.721 0.721 ↓ 0.0 0 721

Index Only Scan using scan_delivers_pk on scan_delivers sdel (cost=0.56..2.56 rows=1 width=59) (actual time=0.001..0.001 rows=0 loops=721)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 0
  • Buffers: shared hit=2884
38. 0.721 0.721 ↓ 0.0 0 721

Index Only Scan using scan_pods_pk on scan_pods spod (cost=0.56..2.54 rows=1 width=57) (actual time=0.001..0.001 rows=0 loops=721)

  • Index Cond: (cons_code = c.code)
  • Heap Fetches: 0
  • Buffers: shared hit=2884
39.          

SubPlan (forNested Loop Left Join)

40. 0.000 0.000 ↓ 0.0 0

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

41. 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 : 7.338 ms
Execution time : 7,228.530 ms