explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jdkG

Settings
# exclusive inclusive rows x rows loops node
1. 0.600 52,761.707 ↑ 1.0 1 1

Aggregate (cost=460,385.59..460,385.60 rows=1 width=8) (actual time=52,761.707..52,761.707 rows=1 loops=1)

  • Output: count(1)
  • Buffers: shared hit=127570 read=721573, temp read=1692 written=1678
2.          

Initplan (for Aggregate)

3. 0.008 0.016 ↑ 1.0 1 1

Aggregate (cost=1.23..1.24 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)

  • Output: array_agg((reading_codes.id)::text)
  • Buffers: shared hit=1
4. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on watto.reading_codes (cost=0.00..1.20 rows=4 width=4) (actual time=0.005..0.008 rows=4 loops=1)

  • Output: reading_codes.id, reading_codes.code, reading_codes.name, reading_codes.index, reading_codes.device_kind_restriction, reading_codes.comment_restriction, reading_codes.reading_restriction, reading_codes.measurement_restriction, reading_codes.is_disabled, reading_codes.is_scored, reading_codes.is_in_progress, reading_codes.allowed_kinds
  • Filter: (reading_codes.code = ANY ('{BO,BS,OD,BP}'::text[]))
  • Rows Removed by Filter: 13
  • Buffers: shared hit=1
5. 0.259 52,761.091 ↓ 21.0 4,340 1

Nested Loop Anti Join (cost=272,199.59..460,383.83 rows=207 width=0) (actual time=52,295.167..52,761.091 rows=4,340 loops=1)

  • Buffers: shared hit=127570 read=721573, temp read=1692 written=1678
6. 4.055 52,672.172 ↓ 21.1 4,433 1

Nested Loop (cost=272,199.30..460,020.89 rows=210 width=4) (actual time=52,295.152..52,672.172 rows=4,433 loops=1)

  • Output: vo.id
  • Inner Unique: true
  • Buffers: shared hit=118684 read=721498, temp read=1692 written=1678
7. 54.703 52,645.422 ↓ 1.9 4,539 1

Hash Join (cost=272,198.88..453,113.83 rows=2,447 width=8) (actual time=52,295.133..52,645.422 rows=4,539 loops=1)

  • Output: vo.session_id, vo.id
  • Inner Unique: true
  • Hash Cond: (vo.delivery_point_id = ro.delivery_point_id)
  • Buffers: shared hit=100421 read=721486, temp read=1692 written=1678
8. 305.484 19,023.994 ↑ 4.4 14,571 1

Bitmap Heap Scan on watto.vindication_orders vo (cost=59,846.80..240,567.15 rows=63,764 width=12) (actual time=18,725.285..19,023.994 rows=14,571 loops=1)

  • Output: vo.session_id, vo.delivery_point_id, vo.id
  • Recheck Cond: ((vo.status = ANY ('{0,1,2}'::integer[])) AND (vo.order_type_id = ANY ('{14,61,51,68,55,72,83,56,57,46,58,47,59,84,78,88,99,15,16,45,48,60,49,50,62,73,121,69,132,143,79,80,70,81,71,110,154,155}'::integer[])))
  • Rows Removed by Index Recheck: 308849
  • Filter: ((vo.due_date <> '2020-09-30 20:00:00'::timestamp without time zone) AND (vo.date <> '2020-09-30 20:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1325
  • Heap Blocks: exact=33271 lossy=29183
  • Buffers: shared hit=68262 read=16227
9. 5.239 18,718.510 ↓ 0.0 0 1

BitmapAnd (cost=59,846.80..59,846.80 rows=63,904 width=0) (actual time=18,718.509..18,718.510 rows=0 loops=1)

  • Buffers: shared hit=5890 read=16145
10. 32.114 32.114 ↓ 1.2 259,971 1

Bitmap Index Scan on "IX_vindication_orders_status" (cost=0.00..4,754.77 rows=216,197 width=0) (actual time=32.114..32.114 rows=259,971 loops=1)

  • Index Cond: (vo.status = ANY ('{0,1,2}'::integer[]))
  • Buffers: shared hit=2202
11. 18,681.157 18,681.157 ↑ 1.0 817,523 1

Bitmap Index Scan on "IX_vindication_orders_order_type_id" (cost=0.00..55,059.89 rows=823,858 width=0) (actual time=18,681.157..18,681.157 rows=817,523 loops=1)

  • Index Cond: (vo.order_type_id = ANY ('{14,61,51,68,55,72,83,56,57,46,58,47,59,84,78,88,99,15,16,45,48,60,49,50,62,73,121,69,132,143,79,80,70,81,71,110,154,155}'::integer[]))
  • Buffers: shared hit=3688 read=16145
12. 92.840 33,566.725 ↓ 15.6 457,252 1

Hash (cost=211,986.52..211,986.52 rows=29,245 width=4) (actual time=33,566.725..33,566.725 rows=457,252 loops=1)

  • Output: ro.delivery_point_id
  • Buckets: 131072 (originally 32768) Batches: 8 (originally 1) Memory Usage: 3073kB
  • Buffers: shared hit=32159 read=705259, temp written=1169
13. 810.592 33,473.885 ↓ 15.6 457,252 1

HashAggregate (cost=211,694.07..211,986.52 rows=29,245 width=4) (actual time=33,342.883..33,473.885 rows=457,252 loops=1)

  • Output: ro.delivery_point_id
  • Group Key: ro.delivery_point_id
  • Buffers: shared hit=32159 read=705259
14. 30,539.033 32,663.293 ↓ 46.3 1,363,936 1

Bitmap Heap Scan on watto.reading_orders ro (cost=5,680.53..211,620.50 rows=29,429 width=4) (actual time=4,430.851..32,663.293 rows=1,363,936 loops=1)

  • Output: ro.delivery_point_id
  • Recheck Cond: (ro.reading_code_ids ?| $0)
  • Rows Removed by Index Recheck: 31731351
  • Filter: ((ro.due_date > '2018-01-01 00:00:00'::timestamp without time zone) AND (ro.status = 3))
  • Rows Removed by Filter: 545986
  • Heap Blocks: exact=45298 lossy=693256
  • Buffers: shared hit=32159 read=705259
15. 2,124.260 2,124.260 ↓ 32.5 2,031,127 1

Bitmap Index Scan on "IX_reading_orders_reading_code_ids" (cost=0.00..5,673.17 rows=62,556 width=0) (actual time=2,124.260..2,124.260 rows=2,031,127 loops=1)

  • Index Cond: (ro.reading_code_ids ?| $0)
  • Buffers: shared hit=30 read=995
16. 22.695 22.695 ↑ 1.0 1 4,539

Index Scan using "PK_vindication_sessions" on watto.vindication_sessions vs (cost=0.42..2.82 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4,539)

  • Output: vs.id, vs.created_timestamp, vs.status, vs.area_id, vs.vindication_type, vs.file_link, vs.created_by_id, vs.closed_timestamp, vs.description, vs.company, vs.rejected_orders
  • Index Cond: (vs.id = vo.session_id)
  • Filter: (vs.status = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=18263 read=12
17. 88.660 88.660 ↓ 0.0 0 4,433

Index Only Scan using "UC_time_slot_reservations_order_id" on watto.time_slot_reservations r (cost=0.29..1.73 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=4,433)

  • Output: r.order_id
  • Index Cond: (r.order_id = vo.id)
  • Heap Fetches: 90
  • Buffers: shared hit=8886 read=75
Planning time : 1.232 ms
Execution time : 52,767.218 ms