explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OAdON

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 107,017.482 ↑ 5.1 15 1

Limit (cost=69,575.85..69,576.04 rows=77 width=106) (actual time=107,017.481..107,017.482 rows=15 loops=1)

  • Buffers: shared hit=38008 read=12836 dirtied=715
2. 0.039 107,017.480 ↑ 5.1 15 1

Sort (cost=69,575.85..69,576.04 rows=77 width=106) (actual time=107,017.479..107,017.480 rows=15 loops=1)

  • Sort Key: "*SELECT* 1".code_1
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=38008 read=12836 dirtied=715
3. 0.027 107,017.441 ↑ 5.1 15 1

HashAggregate (cost=69,572.67..69,573.44 rows=77 width=106) (actual time=107,017.433..107,017.441 rows=15 loops=1)

  • Group Key: "*SELECT* 1".code, "*SELECT* 1".whcontrolpanel, (("*SELECT* 1"."case")::bigint), ('STD'::text), ((0)::bigint), "*SELECT* 1".code_1, "*SELECT* 1".req_type, "*SELECT* 1".name, "*SELECT* 1".reference, "*SELECT* 1".to_char, "*SELECT* 1".rtrim, "*SELECT* 1".dep_id, "*SELECT* 1".dep_id_1, (("*SELECT* 1".parcels)::bigint), "*SELECT* 1".weight, ((0)::bigint)
  • Buffers: shared hit=38005 read=12836 dirtied=715
4. 0.003 107,017.414 ↑ 5.1 15 1

Append (cost=383.72..69,569.59 rows=77 width=106) (actual time=163.048..107,017.414 rows=15 loops=1)

  • Buffers: shared hit=38005 read=12836 dirtied=715
5. 0.014 163.058 ↓ 5.0 15 1

Subquery Scan on *SELECT* 1 (cost=383.72..386.34 rows=3 width=163) (actual time=163.047..163.058 rows=15 loops=1)

  • Buffers: shared hit=35407 read=2 dirtied=632
6. 0.040 163.044 ↓ 5.0 15 1

HashAggregate (cost=383.72..386.31 rows=3 width=163) (actual time=163.040..163.044 rows=15 loops=1)

  • Group Key: c.code, c.req_status, CASE WHEN (((c.req_type)::text = 'PICK'::text) AND ((c.req_status)::text = 'REQ'::text) AND ((c.created).tstamp < ((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '00:15:00'::interval))) THEN 1 WHEN (((c.req_type)::text = 'REPL'::text) AND ((c.req_status)::text = 'REQ'::text) AND (((c.created).tstamp + (c.created).tzone) < (timezone(user_tzoffset('david morgan'::text, (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp without time zone), (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone))::date) AND ((timezone(user_tzoffset('david morgan'::text, (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp without time zone), (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone))::time without time zone > '08:00:00'::time without time zone)) THEN 1 WHEN (((c.req_type)::text = 'PUDO'::text) AND ((c.req_status)::text = 'RDY'::text) AND ((c.ready_tstamp).tstamp < ((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '4 days'::interval))) THEN 1 ELSE 0 END, 'STD'::text, 0, c.code, c.req_type, a.name, c.reference, CASE WHEN (((c.created).tstamp + (c.created).tzone) >= (timezone(user_tzoffset('david morgan'::text, (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp without time zone), (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone))::date) THEN to_char(((c.created).tstamp + (c.created).tzone), 'HH24:MI'::text) ELSE to_char(((c.created).tstamp + (c.created).tzone), 'DD-MM-YYYY'::text) END, rtrim((c.req_status)::text), cd.dep_id, dd.dep_id, c.parcels, c.weight, 0
  • Buffers: shared hit=35407 read=2 dirtied=632
7. 81.321 163.004 ↓ 5.0 15 1

Nested Loop Left Join (cost=1.42..383.60 rows=3 width=163) (actual time=102.140..163.004 rows=15 loops=1)

  • Buffers: shared hit=35407 read=2 dirtied=632
8. 0.002 81.638 ↓ 5.0 15 1

Nested Loop Left Join (cost=1.13..368.09 rows=3 width=160) (actual time=21.711..81.638 rows=15 loops=1)

  • Buffers: shared hit=35238 dirtied=632
9. 0.022 81.606 ↓ 5.0 15 1

Nested Loop (cost=0.85..355.16 rows=3 width=157) (actual time=21.709..81.606 rows=15 loops=1)

  • Buffers: shared hit=35200 dirtied=632
10. 81.479 81.479 ↓ 5.0 15 1

Index Scan using consignments_created_loc_in on consignments c (cost=0.57..342.23 rows=3 width=143) (actual time=21.687..81.479 rows=15 loops=1)

  • Index Cond: ((((created).tstamp + (created).tzone) >= '2019-09-05 00:00:00'::timestamp without time zone) AND (((created).tstamp + (created).tzone) < '2019-09-06 00:00:00'::timestamp without time zone))
  • Filter: ((((pick_by_tstamp).tstamp IS NULL) OR ((req_status)::text <> ALL ('{REQ,ACK}'::text[]))) AND ((req_subtype)::text <> 'HOLD'::text) AND ((req_status)::text <> 'HIDE'::text) AND ((col_dep = 170) OR (del_dep = 170)))
  • Rows Removed by Filter: 17312
  • Buffers: shared hit=35154 dirtied=632
11. 0.105 0.105 ↑ 1.0 1 15

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

  • Index Cond: (code = c.acc_code)
  • Buffers: shared hit=46
12. 0.030 0.030 ↑ 1.0 1 15

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

  • Index Cond: (c.col_dep = code)
  • Buffers: shared hit=38
13. 0.045 0.045 ↑ 1.0 1 15

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

  • Index Cond: (c.del_dep = code)
  • Buffers: shared hit=40
14. 0.002 106,854.353 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=69,181.21..69,183.25 rows=74 width=104) (actual time=106,854.353..106,854.353 rows=0 loops=1)

  • Buffers: shared hit=2598 read=12834 dirtied=83
15. 0.006 106,854.351 ↓ 0.0 0 1

HashAggregate (cost=69,181.21..69,182.51 rows=74 width=104) (actual time=106,854.351..106,854.351 rows=0 loops=1)

  • Group Key: c_1.req_status, 'BATCH'::text, CASE WHEN (rl.orig_cons_code IS NOT NULL) THEN rl.orig_cons_code ELSE '0'::bigint END, c_1.req_type, a_1.name, 'BATCH PICK'::text, rtrim((c_1.req_status)::text), cd_1.dep_id, dd_1.dep_id, CASE WHEN (rl.orig_cons_code IS NOT NULL) THEN rl.orig_cons_code ELSE '0'::bigint END
  • Buffers: shared hit=2598 read=12834 dirtied=83
16. 0.001 106,854.345 ↓ 0.0 0 1

Nested Loop Left Join (cost=55,374.70..69,120.53 rows=74 width=104) (actual time=106,854.345..106,854.345 rows=0 loops=1)

  • Buffers: shared hit=2598 read=12834 dirtied=83
17. 0.002 106,854.344 ↓ 0.0 0 1

Nested Loop Left Join (cost=55,369.76..67,560.53 rows=74 width=96) (actual time=106,854.344..106,854.344 rows=0 loops=1)

  • Buffers: shared hit=2598 read=12834 dirtied=83
18. 0.003 106,854.342 ↓ 0.0 0 1

Nested Loop Left Join (cost=55,369.47..67,261.41 rows=74 width=93) (actual time=106,854.342..106,854.342 rows=0 loops=1)

  • Buffers: shared hit=2598 read=12834 dirtied=83
19. 0.011 106,854.339 ↓ 0.0 0 1

Hash Join (cost=55,369.19..66,962.28 rows=74 width=90) (actual time=106,854.339..106,854.339 rows=0 loops=1)

  • Hash Cond: (c_1.acc_code = a_1.code)
  • Buffers: shared hit=2598 read=12834 dirtied=83
20. 9,186.190 106,853.279 ↓ 0.0 0 1

Bitmap Heap Scan on consignments c_1 (cost=55,232.93..66,825.00 rows=74 width=76) (actual time=106,853.279..106,853.279 rows=0 loops=1)

  • Recheck Cond: ((((col_dep = 170) AND ((created).tstamp > ((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '7 days'::interval))) OR (del_dep = 170)) AND ((req_type)::text = 'PICK'::text) AND ((req_status)::text = ANY ('{REQ,ACK}'::text[])))
  • Rows Removed by Index Recheck: 43384
  • Filter: (((pick_by_tstamp).tstamp IS NOT NULL) AND ((req_subtype)::text <> 'HOLD'::text) AND ((created).tstamp > ((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '7 days'::interval)))
  • Rows Removed by Filter: 443
  • Heap Blocks: exact=265 lossy=2987
  • Buffers: shared hit=2510 read=12834 dirtied=83
21. 65.575 97,667.089 ↓ 0.0 0 1

BitmapAnd (cost=55,232.93..55,232.93 rows=5,785 width=0) (actual time=97,667.089..97,667.089 rows=0 loops=1)

  • Buffers: shared hit=541 read=11551
22. 0.004 29,183.022 ↓ 0.0 0 1

BitmapOr (cost=14,131.68..14,131.68 rows=550,904 width=0) (actual time=29,183.022..29,183.022 rows=0 loops=1)

  • Buffers: shared hit=170 read=4314
23. 20.742 20.742 ↑ 1.5 1,743 1

Bitmap Index Scan on consignments_col_dep_created_utc_in (cost=0.00..167.50 rows=2,692 width=0) (actual time=20.742..20.742 rows=1,743 loops=1)

  • Index Cond: ((col_dep = 170) AND ((created).tstamp > ((((now())::timestamp without time zone + '00:00:00'::interval))::timestamp with time zone - '7 days'::interval)))
  • Buffers: shared hit=18 read=2
24. 29,162.276 29,162.276 ↓ 1.0 549,663 1

Bitmap Index Scan on consignments_sjr5 (cost=0.00..13,964.15 rows=548,211 width=0) (actual time=29,162.276..29,162.276 rows=549,663 loops=1)

  • Index Cond: (del_dep = 170)
  • Buffers: shared hit=152 read=4312
25. 68,418.492 68,418.492 ↑ 8.1 122,553 1

Bitmap Index Scan on consignments_sjr2 (cost=0.00..41,100.97 rows=992,584 width=0) (actual time=68,418.492..68,418.492 rows=122,553 loops=1)

  • Index Cond: (((req_type)::text = 'PICK'::text) AND ((req_status)::text = ANY ('{REQ,ACK}'::text[])))
  • Buffers: shared hit=371 read=7237
26. 0.347 1.049 ↓ 1.0 2,149 1

Hash (cost=109.45..109.45 rows=2,145 width=22) (actual time=1.049..1.049 rows=2,149 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=88
27. 0.702 0.702 ↓ 1.0 2,149 1

Seq Scan on accounts a_1 (cost=0.00..109.45 rows=2,145 width=22) (actual time=0.007..0.702 rows=2,149 loops=1)

  • Buffers: shared hit=88
28. 0.000 0.000 ↓ 0.0 0

Index Scan using depots_pk on depots cd_1 (cost=0.29..4.03 rows=1 width=11) (never executed)

  • Index Cond: (c_1.col_dep = code)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using depots_pk on depots dd_1 (cost=0.29..4.03 rows=1 width=11) (never executed)

  • Index Cond: (c_1.del_dep = code)
30. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on request_links rl (cost=4.94..21.00 rows=8 width=16) (never executed)

  • Recheck Cond: ((c_1.code = new_cons_code) OR (c_1.code = orig_cons_code))
31. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=4.94..4.94 rows=8 width=0) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on request_links_i1 (cost=0.00..2.44 rows=1 width=0) (never executed)

  • Index Cond: (c_1.code = new_cons_code)
33. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on request_links_i2 (cost=0.00..2.49 rows=7 width=0) (never executed)

  • Index Cond: (c_1.code = orig_cons_code)
Planning time : 28.087 ms
Execution time : 107,020.697 ms