explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s6zS

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 892.823 ↓ 19.0 19 1

Limit (cost=1,929.42..1,929.46 rows=1 width=163) (actual time=892.809..892.823 rows=19 loops=1)

  • Buffers: shared hit=1,901 read=77
  • 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), a.name, c.reference, (CASE WHEN (((c.created).tstamp + (c.created).tzone) >= (timezone(user_tzoffset('Lukasz Siewierski'::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, c.parcels, c.weight
2. 0.010 892.820 ↓ 19.0 19 1

Unique (cost=1,929.42..1,929.46 rows=1 width=163) (actual time=892.807..892.820 rows=19 loops=1)

  • Buffers: shared hit=1,901 read=77
3. 0.047 892.810 ↓ 19.0 19 1

Sort (cost=1,929.42..1,929.42 rows=1 width=163) (actual time=892.807..892.810 rows=19 loops=1)

  • Sort Key: dd.dep_id, 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('Lukasz Siewierski'::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('Lukasz Siewierski'::text, (((now())::timestamp without time zone + '00:00:00'::interval))::timestamp without time
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=1,901 read=77
4. 46.279 892.763 ↓ 19.0 19 1

Nested Loop Left Join (cost=1,872.18..1,929.41 rows=1 width=163) (actual time=891.004..892.763 rows=19 loops=1)

  • Join Filter: (c.acc_code = ua.acc_code)
  • Filter: (('Lukasz Siewierski'::text = (c.user_name)::text) OR ('Lukasz Siewierski'::text = (ua.user_name)::text))
  • Rows Removed by Filter: 475
  • Buffers: shared hit=1,901 read=77
5. 0.003 792.847 ↓ 19.0 19 1

Nested Loop Left Join (cost=1,869.70..1,883.81 rows=1 width=178) (actual time=792.638..792.847 rows=19 loops=1)

  • Buffers: shared hit=1,412 read=53
6. 0.017 792.787 ↓ 19.0 19 1

Nested Loop Left Join (cost=1,869.41..1,879.50 rows=1 width=175) (actual time=792.629..792.787 rows=19 loops=1)

  • Buffers: shared hit=1,355 read=53
7. 0.043 792.751 ↓ 19.0 19 1

Nested Loop (cost=1,869.13..1,875.18 rows=1 width=172) (actual time=792.622..792.751 rows=19 loops=1)

  • Buffers: shared hit=1,317 read=53
8. 0.351 792.670 ↓ 19.0 19 1

Bitmap Heap Scan on consignments c (cost=1,868.85..1,870.88 rows=1 width=154) (actual time=792.604..792.670 rows=19 loops=1)

  • Recheck Cond: ((acc_code = 1,754) AND ((req_type)::text = 'PUDO'::text) AND (((created).tstamp + (created).tzone) >= '2019-09-24 00:00:00'::timestamp without time zone) AND (((created).tstamp + (created).tzone) < '2019-09-25 00:00:00'::timestamp without time zone))
  • Filter: (((req_subtype)::text <> 'HOLD'::text) AND ((req_status)::text <> 'HIDE'::text))
  • Heap Blocks: exact=111
  • Buffers: shared hit=1,260 read=53
9. 1.271 792.319 ↓ 0.0 0 1

BitmapAnd (cost=1,868.85..1,868.85 rows=1 width=0) (actual time=792.319..792.319 rows=0 loops=1)

  • Buffers: shared hit=1,149 read=53
10. 740.633 740.633 ↓ 1.6 3,351 1

Bitmap Index Scan on consignments_zz1 (cost=0.00..125.36 rows=2,079 width=0) (actual time=740.633..740.633 rows=3,351 loops=1)

  • Index Cond: ((acc_code = 1,754) AND ((req_type)::text = 'PUDO'::text))
  • Buffers: shared hit=3 read=53
11. 50.415 50.415 ↓ 6.8 287,458 1

Bitmap Index Scan on consignments_created_loc_in (cost=0.00..1,743.24 rows=42,067 width=0) (actual time=50.415..50.415 rows=287,458 loops=1)

  • Index Cond: ((((created).tstamp + (created).tzone) >= '2019-09-24 00:00:00'::timestamp without time zone) AND (((created).tstamp + (created).tzone) < '2019-09-25 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1,146
12. 0.038 0.038 ↑ 1.0 1 19

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

  • Index Cond: (code = 1,754)
  • Buffers: shared hit=57
13. 0.019 0.019 ↓ 0.0 0 19

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

  • Index Cond: (c.col_dep = code)
  • Buffers: shared hit=38
14. 0.057 0.057 ↑ 1.0 1 19

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

  • Index Cond: (c.del_dep = code)
  • Buffers: shared hit=57
15. 46.056 53.637 ↑ 1.0 26 19

Bitmap Heap Scan on user_accounts ua (cost=2.49..44.29 rows=26 width=17) (actual time=0.967..2.823 rows=26 loops=19)

  • Recheck Cond: (acc_code = 1,754)
  • Heap Blocks: exact=399
  • Buffers: shared hit=415 read=22
16. 7.581 7.581 ↑ 1.0 26 19

Bitmap Index Scan on user_accounts_i (cost=0.00..2.48 rows=26 width=0) (actual time=0.399..0.399 rows=26 loops=19)

  • Index Cond: (acc_code = 1,754)
  • Buffers: shared hit=36 read=2
Planning time : 3.097 ms
Execution time : 892.954 ms