explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EP5I

Settings
# exclusive inclusive rows x rows loops node
1. 0.272 1.581 ↓ 29.0 29 1

Nested Loop (cost=11.88..69.63 rows=1 width=167) (actual time=0.511..1.581 rows=29 loops=1)

  • Output: ei.examineserialnum, ei.examineserialnum, pi.patientname, pi.patientspellname, ei.patientid, pi.birthday, si.dicomvalue, ei.patientage, to_char(ei.arrivetime, 'yyyymmdd'::text), '', dt.modality
  • Join Filter: (pi.sex = si.sexvalue)
  • Rows Removed by Join Filter: 58
  • Buffers: shared hit=304
2. 0.046 1.280 ↓ 29.0 29 1

Nested Loop (cost=11.88..68.56 rows=1 width=69) (actual time=0.365..1.280 rows=29 loops=1)

  • Output: ei.examineserialnum, ei.patientid, ei.patientage, ei.arrivetime, pi.patientname, pi.patientspellname, pi.birthday, pi.sex, dt.modality
  • Buffers: shared hit=275
3. 0.018 1.147 ↓ 29.0 29 1

Nested Loop (cost=11.74..68.20 rows=1 width=85) (actual time=0.319..1.147 rows=29 loops=1)

  • Output: ei.examineserialnum, ei.patientid, ei.patientage, ei.arrivetime, ei.deviceid, idi.devid, pi.patientname, pi.patientspellname, pi.birthday, pi.sex, dt.modality
  • Buffers: shared hit=216
4. 0.031 0.636 ↓ 29.0 29 1

Nested Loop (cost=11.32..62.95 rows=1 width=53) (actual time=0.264..0.636 rows=29 loops=1)

  • Output: ei.examineserialnum, ei.patientid, ei.patientage, ei.arrivetime, ei.deviceid, idi.devid, dt.modality
  • Buffers: shared hit=100
5. 0.048 0.489 ↓ 14.5 29 1

Hash Join (cost=11.18..62.30 rows=2 width=58) (actual time=0.233..0.489 rows=29 loops=1)

  • Output: ei.examineserialnum, ei.patientid, ei.patientage, ei.arrivetime, ei.deviceid, ei.devtypeid, idi.devid
  • Hash Cond: (ei.deviceid = idi.devid)
  • Buffers: shared hit=39
6. 0.260 0.290 ↓ 1.2 77 1

Bitmap Heap Scan on public.examineinfo ei (cost=3.24..54.10 rows=65 width=50) (actual time=0.055..0.290 rows=77 loops=1)

  • Output: ei.examineserialnum, ei.patientid, ei.majornum, ei.accessnum, ei.reportid, ei.examinetime, ei.deptid, ei.devtypeid, ei.deviceid, ei.filenum, ei.photouser, ei.lockstate, ei.lockuser, ei.ifemergency, ei.clinicdiagnose, ei.history, ei.reqhospitalname, ei.departmentid, ei.applydoctorname, ei.sickroom, ei.sickbed, ei.weight, ei.height, ei.arrivetime, ei.triagetetime, ei.frststarttime, ei.frstendtime, ei.scndstarttime, ei.scndendtime, ei.studyscription, ei.triagestate, ei.examinestatevalue, ei.ifmasculine, ei.savestate, ei.refusestate, ei.isavailable, ei.createuserid, ei.createtime, ei.operatorid, ei.operatetime, ei.patientname, ei.dicompatientname, ei.examinedescribe, ei.reporthangdocname, ei.departmentname, ei.ifqa, ei.checkid, ei.applydoctorcode, ei.reporthang, ei.reporthangdocid, ei.ismenopause, ei.lastmensesdate, ei.ifconsultation, ei.expmark, ei.imageprint, ei.reportprint, ei.applystudyid, ei.patientage, ei.applypatientid, ei.hisid, ei.hispatientid, ei.tohyx, ei.hisitemid, ei.applyid, ei.institutionid, ei.institutionname
  • Recheck Cond: (ei.examinestatevalue = 300)
  • Filter: (ei.arrivetime <= '2019-12-16 23:59:59'::timestamp without time zone)
  • Heap Blocks: exact=31
  • Buffers: shared hit=32
7. 0.030 0.030 ↑ 1.0 77 1

Bitmap Index Scan on idx_t_examineinfo_examinestatevalue (cost=0.00..3.23 rows=77 width=0) (actual time=0.030..0.030 rows=77 loops=1)

  • Index Cond: (ei.examinestatevalue = 300)
  • Buffers: shared hit=1
8. 0.003 0.151 ↑ 1.0 1 1

Hash (cost=7.93..7.93 rows=1 width=8) (actual time=0.151..0.151 rows=1 loops=1)

  • Output: idi.devid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=7
9. 0.007 0.148 ↑ 1.0 1 1

HashAggregate (cost=7.92..7.93 rows=1 width=8) (actual time=0.147..0.148 rows=1 loops=1)

  • Output: idi.devid
  • Group Key: idi.devid
  • Buffers: shared hit=7
10. 0.002 0.141 ↑ 1.0 1 1

Nested Loop (cost=4.69..7.92 rows=1 width=8) (actual time=0.131..0.141 rows=1 loops=1)

  • Output: idi.devid
  • Buffers: shared hit=7
11. 0.037 0.129 ↑ 1.0 1 1

Hash Join (cost=4.55..7.62 rows=1 width=16) (actual time=0.120..0.129 rows=1 loops=1)

  • Output: idi.devid, idpt.hospid
  • Hash Cond: (idpt.deptid = idi.deptid)
  • Buffers: shared hit=5
12. 0.036 0.036 ↓ 1.2 91 1

Seq Scan on public.departmentinfo idpt (cost=0.00..2.77 rows=77 width=16) (actual time=0.010..0.036 rows=91 loops=1)

  • Output: idpt.hospid, idpt.deptcode, idpt.deptname, idpt.isavailable, idpt.otherdeptid, idpt.operatorid, idpt.operatetime, idpt.deptmarks, idpt.deptid
  • Buffers: shared hit=2
13. 0.008 0.056 ↑ 1.0 1 1

Hash (cost=4.54..4.54 rows=1 width=16) (actual time=0.056..0.056 rows=1 loops=1)

  • Output: idi.deptid, idi.devid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
14. 0.048 0.048 ↑ 1.0 1 1

Seq Scan on public.deviceinfo idi (cost=0.00..4.54 rows=1 width=16) (actual time=0.030..0.048 rows=1 loops=1)

  • Output: idi.deptid, idi.devid
  • Filter: ((idi.aetitle)::text = 'NC_CT'::text)
  • Rows Removed by Filter: 127
  • Buffers: shared hit=3
15. 0.010 0.010 ↑ 1.0 1 1

Index Scan using "hospPK" on public.hospitalinfo ihi (cost=0.14..0.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: ihi.hospid, ihi.hospname, ihi.hospshortname, ihi.hospcode, ihi.hospsourceid, ihi.hospdomain, ihi.hospareacode, ihi.hospaddinfo, ihi.isavailable, ihi.operatorid, ihi.operatetime, ihi.hospreqmarks
  • Index Cond: (ihi.hospid = idpt.hospid)
  • Filter: ((ihi.hospcode)::text = 'pahyncqjd'::text)
  • Buffers: shared hit=2
16. 0.116 0.116 ↑ 1.0 1 29

Index Scan using devicetype_id on public.devicetype dt (cost=0.14..0.31 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=29)

  • Output: dt.devtypeid, dt.devtypename, dt.modality, dt.isavailable, dt.operatorid, dt.operatetime, dt.devtypemarks
  • Index Cond: (dt.devtypeid = ei.devtypeid)
  • Filter: ((dt.modality)::text = 'CT'::text)
  • Buffers: shared hit=61
17. 0.493 0.493 ↑ 1.0 1 29

Index Scan using patientinfo_pkey on public.patientinfo pi (cost=0.41..5.24 rows=1 width=42) (actual time=0.016..0.017 rows=1 loops=29)

  • Output: pi.patientid, pi.patientname, pi.patientspellname, pi.birthday, pi.idnum, pi.phonenum, pi.nationality, pi.folk, pi.address, pi.patienttypevalue, pi.societyid, pi.operatorid, pi.operatetime, pi.email, pi.sex
  • Index Cond: ((pi.patientid)::text = (ei.patientid)::text)
  • Buffers: shared hit=116
18. 0.087 0.087 ↑ 1.0 1 29

Index Only Scan using deviceinfo_id on public.deviceinfo di (cost=0.14..0.35 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=29)

  • Output: di.devid
  • Index Cond: (di.devid = ei.deviceid)
  • Heap Fetches: 29
  • Buffers: shared hit=59
19. 0.029 0.029 ↑ 1.0 3 29

Seq Scan on public.sexinfo si (cost=0.00..1.03 rows=3 width=38) (actual time=0.001..0.001 rows=3 loops=29)

  • Output: si.sexvalue, si.sexdisplay, si.sextype, si.id, si.dicomvalue
  • Buffers: shared hit=29