explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zyt1

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 102,987.806 ↓ 4.2 17 1

Sort (cost=18,372.38..18,372.39 rows=4 width=52) (actual time=102,987.805..102,987.806 rows=17 loops=1)

  • Sort Key: c.col_date, c.reference, ((i.code)::text)
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=21945 read=16657 dirtied=250
2. 0.024 102,987.751 ↓ 4.2 17 1

HashAggregate (cost=18,372.30..18,372.34 rows=4 width=52) (actual time=102,987.748..102,987.751 rows=17 loops=1)

  • Group Key: ((i.code)::text), ag2.name, c.reference, b.bin_ref, c.col_date
  • Buffers: shared hit=21939 read=16657 dirtied=250
3. 0.003 102,987.727 ↓ 4.2 17 1

Append (cost=2,310.84..18,372.25 rows=4 width=52) (actual time=102,987.721..102,987.727 rows=17 loops=1)

  • Buffers: shared hit=21939 read=16657 dirtied=250
4. 0.000 11,252.629 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,310.84..9,295.68 rows=1 width=50) (actual time=11,252.629..11,252.629 rows=0 loops=1)

  • Buffers: shared hit=5738 read=1509 dirtied=13
5. 0.001 11,252.629 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,310.41..9,291.52 rows=1 width=41) (actual time=11,252.629..11,252.629 rows=0 loops=1)

  • Buffers: shared hit=5738 read=1509 dirtied=13
6. 0.000 11,252.628 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,309.99..9,287.07 rows=1 width=31) (actual time=11,252.628..11,252.628 rows=0 loops=1)

  • Buffers: shared hit=5738 read=1509 dirtied=13
7. 0.000 11,252.628 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,309.43..9,282.77 rows=1 width=27) (actual time=11,252.628..11,252.628 rows=0 loops=1)

  • Buffers: shared hit=5738 read=1509 dirtied=13
8. 0.027 11,252.628 ↓ 0.0 0 1

Nested Loop (cost=2,308.87..9,276.59 rows=1 width=27) (actual time=11,252.628..11,252.628 rows=0 loops=1)

  • Buffers: shared hit=5738 read=1509 dirtied=13
9. 0.004 0.073 ↓ 3.5 14 1

Nested Loop (cost=5.07..28.96 rows=4 width=4) (actual time=0.046..0.073 rows=14 loops=1)

  • Buffers: shared hit=21
10. 0.021 0.021 ↑ 1.0 1 1

Index Scan using agents_pk on agents ag (cost=0.42..4.44 rows=1 width=8) (actual time=0.019..0.021 rows=1 loops=1)

  • Index Cond: (code = 131668)
  • Buffers: shared hit=4
11. 0.028 0.048 ↓ 1.2 14 1

Bitmap Heap Scan on accounts a (cost=4.66..24.40 rows=12 width=8) (actual time=0.025..0.048 rows=14 loops=1)

  • Recheck Cond: ((ag.acc_code = code) OR (ag.acc_group = acc_group))
  • Heap Blocks: exact=13
  • Buffers: shared hit=17
12. 0.001 0.020 ↓ 0.0 0 1

BitmapOr (cost=4.66..4.66 rows=12 width=0) (actual time=0.020..0.020 rows=0 loops=1)

  • Buffers: shared hit=4
13. 0.006 0.006 ↑ 1.0 1 1

Bitmap Index Scan on accounts_pk (cost=0.00..2.29 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (ag.acc_code = code)
  • Buffers: shared hit=2
14. 0.013 0.013 ↓ 1.2 13 1

Bitmap Index Scan on accounts_3i (cost=0.00..2.36 rows=11 width=0) (actual time=0.013..0.013 rows=13 loops=1)

  • Index Cond: (ag.acc_group = acc_group)
  • Buffers: shared hit=2
15. 107.674 11,252.528 ↓ 0.0 0 14

Bitmap Heap Scan on consignments c (cost=2,303.79..2,311.90 rows=1 width=31) (actual time=803.752..803.752 rows=0 loops=14)

  • Recheck Cond: ((acc_code = a.code) AND ((req_type)::text = 'PICK'::text) AND (col_dep = 1790) AND ((created).tstamp > '2019-07-27 00:00:00'::timestamp without time zone))
  • Filter: (((ready_tstamp).tstamp > '2019-08-06 00:00:00'::timestamp without time zone) AND ((req_subtype)::text = 'ENGW'::text) AND ((req_status)::text = 'RDY'::text))
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=292
  • Buffers: shared hit=5717 read=1509 dirtied=13
16. 4.662 11,144.854 ↓ 0.0 0 14

BitmapAnd (cost=2,303.79..2,303.79 rows=4 width=0) (actual time=796.061..796.061 rows=0 loops=14)

  • Buffers: shared hit=5429 read=1505
17. 10,517.346 10,517.346 ↑ 4.9 3,976 14

Bitmap Index Scan on consignments_zz1 (cost=0.00..1,088.43 rows=19,489 width=0) (actual time=751.239..751.239 rows=3,976 loops=14)

  • Index Cond: ((acc_code = a.code) AND ((req_type)::text = 'PICK'::text))
  • Buffers: shared hit=86 read=1423
18. 622.846 622.846 ↓ 4.2 82,838 7

Bitmap Index Scan on consignments_col_dep_created_utc_in (cost=0.00..1,215.11 rows=19,654 width=0) (actual time=88.978..88.978 rows=82,838 loops=7)

  • Index Cond: ((col_dep = 1790) AND ((created).tstamp > '2019-07-27 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=5343 read=82
19. 0.000 0.000 ↓ 0.0 0

Index Scan using request_items_cons_code_idx on request_items ri (cost=0.56..5.65 rows=53 width=16) (never executed)

  • Index Cond: (cons_code = c.code)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using items_pk on items i (cost=0.56..4.29 rows=1 width=12) (never executed)

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

Index Scan using agents_pk on agents ag2 (cost=0.42..4.44 rows=1 width=18) (never executed)

  • Index Cond: (c.agent_code = code)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using bins_pk on bins b (cost=0.42..4.15 rows=1 width=17) (never executed)

  • Index Cond: (i.bin_code = code)
23. 0.056 91,735.095 ↓ 5.7 17 1

HashAggregate (cost=9,076.46..9,076.53 rows=3 width=52) (actual time=91,735.090..91,735.095 rows=17 loops=1)

  • Group Key: (((p.cons_code)::text || '/'::text) || (p.item)::text), ra.name, c_1.reference, b_1.bin_ref, c_1.col_date
  • Buffers: shared hit=16201 read=15148 dirtied=237
24. 0.056 91,735.039 ↓ 5.7 17 1

Nested Loop Left Join (cost=2.54..9,076.42 rows=3 width=52) (actual time=77.150..91,735.039 rows=17 loops=1)

  • Buffers: shared hit=16201 read=15148 dirtied=237
25. 0.011 91,657.446 ↓ 17.0 17 1

Nested Loop Left Join (cost=1.55..9,067.92 rows=1 width=39) (actual time=62.879..91,657.446 rows=17 loops=1)

  • Buffers: shared hit=16053 read=15140 dirtied=237
26. 0.062 91,586.069 ↓ 17.0 17 1

Nested Loop Left Join (cost=0.98..9,061.48 rows=1 width=37) (actual time=56.034..91,586.069 rows=17 loops=1)

  • Buffers: shared hit=15992 read=15131 dirtied=237
27. 91,585.905 91,585.905 ↓ 17.0 17 1

Index Scan using consignments_zz1 on consignments c_1 (cost=0.57..9,057.04 rows=1 width=27) (actual time=56.009..91,585.905 rows=17 loops=1)

  • Index Cond: ((acc_code = 1631) AND ((req_type)::text = 'PUDO'::text))
  • Filter: (((created).tstamp > '2019-07-27 00:00:00'::timestamp without time zone) AND ((ready_tstamp).tstamp > '2019-08-06 00:00:00'::timestamp without time zone) AND ((req_status)::text = 'RDY'::text) AND (del_dep = 1790))
  • Rows Removed by Filter: 29715
  • Buffers: shared hit=15924 read=15131 dirtied=237
28. 0.102 0.102 ↑ 1.0 1 17

Index Scan using agents_pk on agents ra (cost=0.42..4.44 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=17)

  • Index Cond: (c_1.agent_code = code)
  • Buffers: shared hit=68
29. 71.366 71.366 ↑ 68.0 1 17

Index Only Scan using parcels_pk on parcels p (cost=0.57..5.76 rows=68 width=10) (actual time=4.197..4.198 rows=1 loops=17)

  • Index Cond: (cons_code = c_1.code)
  • Heap Fetches: 1
  • Buffers: shared hit=61 read=9
30. 0.051 77.537 ↑ 1.0 1 17

Nested Loop Left Join (cost=0.99..8.44 rows=1 width=29) (actual time=4.560..4.561 rows=1 loops=17)

  • Buffers: shared hit=148 read=8
31. 24.905 24.905 ↑ 1.0 1 17

Index Scan using parcel_extras_pk on parcel_extras pe (cost=0.56..4.29 rows=1 width=20) (actual time=1.465..1.465 rows=1 loops=17)

  • Index Cond: ((p.cons_code = cons_code) AND (p.item = item_code))
  • Buffers: shared hit=86 read=2
32. 52.581 52.581 ↑ 1.0 1 17

Index Scan using bins_pk on bins b_1 (cost=0.42..4.14 rows=1 width=17) (actual time=3.093..3.093 rows=1 loops=17)

  • Index Cond: (pe.bin_code = code)
  • Buffers: shared hit=62 read=6
Planning time : 222.473 ms
Execution time : 102,988.065 ms