explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9gjQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 2.221 ↑ 1.0 1 1

Aggregate (cost=80.30..80.31 rows=1 width=32) (actual time=2.221..2.221 rows=1 loops=1)

  • Output: CASE WHEN (NOT $3) THEN NULL::text WHEN $5 THEN 'red'::text ELSE CASE WHEN (((count(DISTINCT (opendoorrecord.opentime)::date) FILTER (WHERE ((opendoorrecord.opentime)::text > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text)) >= 5) IS TRUE) AND bool_or(((opendoorrecord.opentime)::text < (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text))) THEN 'green'::text ELSE 'yellow'::text END END
2.          

CTE keys

3. 0.007 0.425 ↓ 1.7 72 1

Append (cost=1.50..76.18 rows=43 width=50) (actual time=0.054..0.425 rows=72 loops=1)

4. 0.001 0.017 ↓ 0.0 0 1

Nested Loop (cost=1.50..4.46 rows=2 width=54) (actual time=0.017..0.017 rows=0 loops=1)

  • Output: lockkeyinfo.houseid, lockkeyinfo.lockid
  • Inner Unique: true
5. 0.001 0.016 ↓ 0.0 0 1

Bitmap Heap Scan on alpubdb.lockkeyinfo (cost=1.09..1.91 rows=4 width=54) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: lockkeyinfo.keyid, lockkeyinfo.lockid, lockkeyinfo.useraccount, lockkeyinfo.houseid, lockkeyinfo.authaccount, lockkeyinfo.startdate, lockkeyinfo.expireddate, lockkeyinfo.keytype, lockkeyinfo.freezelabel, lockkeyinfo.keystate, lockkeyinfo.holdkeynum, lockkeyinfo.userrole, lockkeyinfo.openmode, lockkeyinfo.openpwd, lockkeyinfo.applytime, lockkeyinfo.authtime, lockkeyinfo.enableflag, lockkeyinfo.keyholder, lockkeyinfo.keysign, lockkeyinfo.memo, lockkeyinfo.confirmkeytime
  • Recheck Cond: (('15157236362'::text = (lockkeyinfo.keyholder)::text) OR ('15157236362'::text = (lockkeyinfo.useraccount)::text))
  • Filter: (lockkeyinfo.userrole <> 255)
6. 0.000 0.015 ↓ 0.0 0 1

BitmapOr (cost=1.09..1.09 rows=7 width=0) (actual time=0.015..0.015 rows=0 loops=1)

7. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on lockkeyinfo_keyholder_idx (cost=0.00..0.54 rows=3 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((lockkeyinfo.keyholder)::text = '15157236362'::text)
8. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on lockkeyinfo_useraccount_idx (cost=0.00..0.54 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((lockkeyinfo.useraccount)::text = '15157236362'::text)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using houseinfo_pkey on alpubdb.houseinfo (cost=0.42..0.64 rows=1 width=35) (never executed)

  • Output: houseinfo.houseid, houseinfo.customid, houseinfo.housedirid, houseinfo.ownername, houseinfo.ownerphone, houseinfo.ownerid, houseinfo.regionaddr, houseinfo.villagetown, houseinfo.community, houseinfo.street, houseinfo.xqname, houseinfo.building, houseinfo.unit, houseinfo.detailaddr, houseinfo.longitude, houseinfo.latitude, houseinfo.area, houseinfo.housetype, houseinfo.maxpeople, houseinfo.rent, houseinfo.photo1, houseinfo.photo2, houseinfo.photo3, houseinfo.photo4, houseinfo.housestatus, houseinfo.housenumber, houseinfo.xqpcs, houseinfo.gldwdm, houseinfo.xzqhdm, houseinfo.jwq, houseinfo.jwqdm, houseinfo.letdate, houseinfo.agentname, houseinfo.agentphone, houseinfo.agentid, houseinfo.lockfee, houseinfo.modifytime, houseinfo.memo, houseinfo.createmanageruserid, houseinfo.createtime, houseinfo.modifymangeuserid
  • Index Cond: ((houseinfo.houseid)::text = (lockkeyinfo.houseid)::text)
  • Filter: ((houseinfo.xzqhdm)::text ~~ '330522%'::text)
10. 0.034 0.401 ↓ 1.8 72 1

Nested Loop (cost=2.25..71.07 rows=41 width=50) (actual time=0.037..0.401 rows=72 loops=1)

  • Output: keyhistoryinfo.houseid, keyhistoryinfo.lockid
  • Inner Unique: true
11. 0.133 0.151 ↑ 1.3 72 1

Bitmap Heap Scan on alpubdb.keyhistoryinfo (cost=1.83..13.46 rows=91 width=50) (actual time=0.028..0.151 rows=72 loops=1)

  • Output: keyhistoryinfo.keyid, keyhistoryinfo.lockid, keyhistoryinfo.useraccount, keyhistoryinfo.authaccount, keyhistoryinfo.startdate, keyhistoryinfo.expireddate, keyhistoryinfo.keytype, keyhistoryinfo.keystate, keyhistoryinfo.holdkeynum, keyhistoryinfo.userrole, keyhistoryinfo.freezelabel, keyhistoryinfo.openmode, keyhistoryinfo.openpwd, keyhistoryinfo.applytime, keyhistoryinfo.authtime, keyhistoryinfo.stoptime, keyhistoryinfo.operator, keyhistoryinfo.address, keyhistoryinfo.keyholder, keyhistoryinfo.keysign, keyhistoryinfo.houseid, keyhistoryinfo.memo, keyhistoryinfo.id
  • Recheck Cond: (('15157236362'::text = (keyhistoryinfo.keyholder)::text) OR ('15157236362'::text = (keyhistoryinfo.useraccount)::text))
  • Filter: (keyhistoryinfo.userrole <> 255)
  • Heap Blocks: exact=70
12. 0.000 0.018 ↓ 0.0 0 1

BitmapOr (cost=1.83..1.83 rows=99 width=0) (actual time=0.018..0.018 rows=0 loops=1)

13. 0.006 0.006 ↑ 4.0 1 1

Bitmap Index Scan on keyhistoryinfo_keyholder_idx (cost=0.00..0.55 rows=4 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((keyhistoryinfo.keyholder)::text = '15157236362'::text)
14. 0.012 0.012 ↑ 1.3 72 1

Bitmap Index Scan on keyhistoryinfo_useraccount_idx (cost=0.00..1.23 rows=95 width=0) (actual time=0.011..0.012 rows=72 loops=1)

  • Index Cond: ((keyhistoryinfo.useraccount)::text = '15157236362'::text)
15. 0.216 0.216 ↑ 1.0 1 72

Index Scan using houseinfo_pkey on alpubdb.houseinfo houseinfo_1 (cost=0.42..0.63 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=72)

  • Output: houseinfo_1.houseid, houseinfo_1.customid, houseinfo_1.housedirid, houseinfo_1.ownername, houseinfo_1.ownerphone, houseinfo_1.ownerid, houseinfo_1.regionaddr, houseinfo_1.villagetown, houseinfo_1.community, houseinfo_1.street, houseinfo_1.xqname, houseinfo_1.building, houseinfo_1.unit, houseinfo_1.detailaddr, houseinfo_1.longitude, houseinfo_1.latitude, houseinfo_1.area, houseinfo_1.housetype, houseinfo_1.maxpeople, houseinfo_1.rent, houseinfo_1.photo1, houseinfo_1.photo2, houseinfo_1.photo3, houseinfo_1.photo4, houseinfo_1.housestatus, houseinfo_1.housenumber, houseinfo_1.xqpcs, houseinfo_1.gldwdm, houseinfo_1.xzqhdm, houseinfo_1.jwq, houseinfo_1.jwqdm, houseinfo_1.letdate, houseinfo_1.agentname, houseinfo_1.agentphone, houseinfo_1.agentid, houseinfo_1.lockfee, houseinfo_1.modifytime, houseinfo_1.memo, houseinfo_1.createmanageruserid, houseinfo_1.createtime, houseinfo_1.modifymangeuserid
  • Index Cond: ((houseinfo_1.houseid)::text = (keyhistoryinfo.houseid)::text)
  • Filter: ((houseinfo_1.xzqhdm)::text ~~ '330522%'::text)
16.          

Initplan (for Aggregate)

17. 0.001 0.001 ↑ 43.0 1 1

CTE Scan on keys keys_1 (cost=0.00..0.86 rows=43 width=0) (actual time=0.001..0.001 rows=1 loops=1)

18. 0.083 1.083 ↓ 0.0 0 1

Nested Loop (cost=0.42..70.95 rows=39 width=0) (actual time=1.083..1.083 rows=0 loops=1)

19. 0.424 0.424 ↓ 1.7 72 1

CTE Scan on keys keys_2 (cost=0.00..0.86 rows=43 width=98) (actual time=0.000..0.424 rows=72 loops=1)

  • Output: keys_2.houseid, keys_2.lockid
20. 0.576 0.576 ↓ 0.0 0 72

Index Scan using billrecord_lockid_idx on alpubdb.billrecord (cost=0.42..1.62 rows=1 width=19) (actual time=0.008..0.008 rows=0 loops=72)

  • Output: billrecord.billid, billrecord.billname, billrecord.houseid, billrecord.customid, billrecord.houseaddr, billrecord.lockid, billrecord.appusername, billrecord.appaccount, billrecord.amount, billrecord.monthlyfee, billrecord.paymonths, billrecord.discountamount, billrecord.billcreatetime, billrecord.billendtime, billrecord.payendtime, billrecord.servicetype, billrecord.paytype, billrecord.billpaydate, billrecord.billstate, billrecord.paymode, billrecord.payername, billrecord.payaccount, billrecord.recipientname, billrecord.recipientaccount, billrecord.poundage, billrecord.poundageparter, billrecord.payno, billrecord.payorderstring, billrecord.paytime, billrecord.payclosetime, billrecord.paystatus, billrecord.errmessage, billrecord.verifystate, billrecord.verifier, billrecord.billoldstate, billrecord.curbillnum, billrecord.verifytime, billrecord.memo, billrecord.createmanageruserid, billrecord.createtime, billrecord.modifymangeuserid, billrecord.modifytime
  • Index Cond: ((billrecord.lockid)::text = (keys_2.lockid)::text)
  • Filter: ((billrecord.billstate = 0) AND ((billrecord.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text))
  • Rows Removed by Filter: 10
21. 1.051 1.121 ↓ 0.0 0 1

Hash Join (cost=0.79..1.82 rows=1 width=20) (actual time=1.120..1.121 rows=0 loops=1)

  • Output: opendoorrecord.opentime
  • Hash Cond: ((keys.lockid)::text = (opendoorrecord.lockid)::text)
22. 0.055 0.055 ↑ 43.0 1 1

CTE Scan on keys (cost=0.00..0.86 rows=43 width=98) (actual time=0.055..0.055 rows=1 loops=1)

  • Output: keys.houseid, keys.lockid
23. 0.000 0.015 ↓ 0.0 0 1

Hash (cost=0.78..0.78 rows=1 width=39) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: opendoorrecord.opentime, opendoorrecord.lockid
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.015 0.015 ↓ 0.0 0 1

Index Only Scan using opendoorrecord_userid_lockid_opentime_idx on alpubdb.opendoorrecord (cost=0.56..0.78 rows=1 width=39) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: opendoorrecord.opentime, opendoorrecord.lockid
  • Index Cond: ((opendoorrecord.userid = '15157236362'::text) AND (opendoorrecord.lockid = ANY ('{AILOCK_db38bbf2f8f0}'::text[])))
  • Heap Fetches: 0
Planning time : 0.764 ms
Execution time : 2.299 ms