explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gCTR

Settings
# exclusive inclusive rows x rows loops node
1. 865.035 10,290.768 ↑ 1.0 1 1

Aggregate (cost=1,016,611.93..1,016,611.97 rows=1 width=48) (actual time=10,290.768..10,290.768 rows=1 loops=1)

  • Output: count(hcs.id), (count(hcs.id) / 24), count(DISTINCT rr.id), (count(DISTINCT rr.id) / 24), count(DISTINCT il.id), (count(DISTINCT il.id) / 24)
  • Buffers: shared hit=20656 read=139009 written=129, temp read=6452 written=6446
  • I/O Timings: read=5350.983 write=1.259
2. 266.841 9,425.733 ↓ 1.1 596,355 1

Hash Left Join (cost=123,053.81..1,008,312.47 rows=553,297 width=18) (actual time=1,456.803..9,425.733 rows=596,355 loops=1)

  • Output: hcs.id, rr.id, il.id
  • Hash Cond: (rr.il_id = il.id)
  • Buffers: shared hit=20656 read=139009 written=129, temp read=4848 written=4842
  • I/O Timings: read=5350.983 write=1.259
3. 525.323 9,138.748 ↓ 1.1 596,355 1

Hash Left Join (cost=118,658.68..985,243.58 rows=553,297 width=18) (actual time=1,436.406..9,138.748 rows=596,355 loops=1)

  • Output: hcs.id, rr.id, rr.il_id
  • Hash Cond: (hcs.reimbursement_request_id = rr.id)
  • Buffers: shared hit=18930 read=139009 written=129, temp read=4848 written=4842
  • I/O Timings: read=5350.983 write=1.259
4. 7,180.775 7,191.177 ↓ 1.1 596,355 1

Bitmap Heap Scan on schema_rr_adm.health_care_service hcs (cost=5,793.81..843,502.93 rows=553,297 width=12) (actual time=13.363..7,191.177 rows=596,355 loops=1)

  • Output: hcs.id, hcs.reimbursement_request_id
  • Recheck Cond: ((hcs.modification_date < current_date) AND (hcs.modification_date > (current_date - 1)))
  • Rows Removed by Index Recheck: 2343590
  • Heap Blocks: lossy=111232
  • Buffers: shared hit=17033 read=94243 written=129
  • I/O Timings: read=4615.361 write=1.259
5. 10.402 10.402 ↓ 2.0 1,112,320 1

Bitmap Index Scan on idx_hcs_moddate (cost=0.00..5,655.48 rows=553,297 width=0) (actual time=10.402..10.402 rows=1,112,320 loops=1)

  • Index Cond: ((hcs.modification_date < current_date) AND (hcs.modification_date > (current_date - 1)))
  • Buffers: shared hit=44
6. 315.264 1,422.248 ↓ 1.0 982,463 1

Hash (cost=76,136.86..76,136.86 rows=982,462 width=12) (actual time=1,422.248..1,422.248 rows=982,463 loops=1)

  • Output: rr.id, rr.il_id
  • Buckets: 524288 Batches: 4 Memory Usage: 14981kB
  • Buffers: shared hit=1897 read=44766, temp written=2970
  • I/O Timings: read=735.622
7. 1,106.984 1,106.984 ↓ 1.0 982,463 1

Seq Scan on schema_rr_adm.reimb_requests rr (cost=0.00..76,136.86 rows=982,462 width=12) (actual time=3.349..1,106.984 rows=982,463 loops=1)

  • Output: rr.id, rr.il_id
  • Buffers: shared hit=1897 read=44766
  • I/O Timings: read=735.622
8. 10.193 20.144 ↑ 1.0 42,706 1

Hash (cost=3,007.18..3,007.18 rows=42,706 width=6) (actual time=20.144..20.144 rows=42,706 loops=1)

  • Output: il.id
  • Buckets: 65536 Batches: 1 Memory Usage: 2119kB
  • Buffers: shared hit=1726
9. 9.951 9.951 ↑ 1.0 42,706 1

Seq Scan on schema_rr_adm.invoice_list il (cost=0.00..3,007.18 rows=42,706 width=6) (actual time=0.008..9.951 rows=42,706 loops=1)

  • Output: il.id
  • Buffers: shared hit=1726
Planning time : 1.413 ms
Execution time : 10,292.797 ms