explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y0Vf

Settings
# exclusive inclusive rows x rows loops node
1. 2.105 23,020.964 ↑ 1.0 1 1

Aggregate (cost=169,884.58..169,884.59 rows=1 width=32) (actual time=23,020.964..23,020.964 rows=1 loops=1)

  • Output: CASE WHEN (NOT $4) 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
  • Functions: 84
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 20.922 ms, Inlining 0.000 ms, Optimization 15.030 ms, Emission 357.865 ms, Total 393.818 ms
2.          

CTE keys

3. 97.354 1,913.045 ↓ 0.0 0 1

Gather (cost=1,000.42..6,408.11 rows=6 width=109) (actual time=1,903.848..1,913.045 rows=0 loops=1)

  • Output: keyhistoryinfo.houseid, keyhistoryinfo.lockid, (COALESCE(keyhistoryinfo.keyholder, keyhistoryinfo.useraccount))
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 18
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 6.690 ms, Inlining 0.000 ms, Optimization 2.723 ms, Emission 90.765 ms, Total 100.178 ms
  • JIT for worker 1:
  • Functions: 18
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 6.383 ms, Inlining 0.000 ms, Optimization 7.489 ms, Emission 100.849 ms, Total 114.721 ms
4. 0.003 1,815.691 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.42..5,407.51 rows=6 width=109) (actual time=1,815.691..1,815.691 rows=0 loops=3)

  • Worker 0: actual time=1773.975..1773.976 rows=0 loops=1
  • Worker 1: actual time=1772.585..1772.585 rows=0 loops=1
5. 0.001 735.979 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.42..2,990.36 rows=2 width=108) (actual time=735.979..735.979 rows=0 loops=3)

  • Output: keyhistoryinfo.houseid, keyhistoryinfo.lockid, COALESCE(keyhistoryinfo.keyholder, keyhistoryinfo.useraccount)
  • Inner Unique: true
  • Worker 0: actual time=1773.973..1773.973 rows=0 loops=1
  • Worker 1: actual time=217.013..217.013 rows=0 loops=1
6. 735.978 735.978 ↓ 0.0 0 3 / 3

Parallel Seq Scan on alpubdb.keyhistoryinfo (cost=0.00..2,988.44 rows=3 width=74) (actual time=735.978..735.978 rows=0 loops=3)

  • 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
  • Filter: ((keyhistoryinfo.userrole <> 255) AND (('13800138000'::text = (keyhistoryinfo.keyholder)::text) OR ('13800138000'::text = (keyhistoryinfo.useraccount)::text)))
  • Rows Removed by Filter: 86211
  • Worker 0: actual time=1773.971..1773.971 rows=0 loops=1
  • Worker 1: actual time=217.013..217.013 rows=0 loops=1
7. 0.000 0.000 ↓ 0.0 0 / 3

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 = (keyhistoryinfo.houseid)::text)
  • Filter: ((houseinfo.xzqhdm)::text ~~ '330522%'::text)
8. 0.001 1,079.709 ↓ 0.0 0 2 / 3

Nested Loop (cost=0.42..2,417.07 rows=1 width=112) (actual time=1,619.563..1,619.563 rows=0 loops=2)

  • Output: lockkeyinfo.houseid, lockkeyinfo.lockid, COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount)
  • Inner Unique: true
  • Worker 1: actual time=1555.570..1555.570 rows=0 loops=1
9. 1,079.708 1,079.708 ↓ 0.0 0 2 / 3

Parallel Seq Scan on alpubdb.lockkeyinfo (cost=0.00..2,415.79 rows=2 width=78) (actual time=1,619.562..1,619.562 rows=0 loops=2)

  • 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
  • Filter: ((lockkeyinfo.userrole <> 255) AND (('13800138000'::text = (lockkeyinfo.keyholder)::text) OR ('13800138000'::text = (lockkeyinfo.useraccount)::text)))
  • Rows Removed by Filter: 102542
  • Worker 1: actual time=1555.569..1555.569 rows=0 loops=1
10. 0.000 0.000 ↓ 0.0 0 / 3

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

  • 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 = (lockkeyinfo.houseid)::text)
  • Filter: ((houseinfo_1.xzqhdm)::text ~~ '330522%'::text)
11.          

Initplan (for Aggregate)

12. 1,903.851 1,903.851 ↓ 0.0 0 1

CTE Scan on keys (cost=0.00..0.12 rows=6 width=0) (actual time=1,903.851..1,903.851 rows=0 loops=1)

13. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.20..13,999.63 rows=6 width=0) (never executed)

  • Hash Cond: ((billrecord.houseid)::text = (keys_1.houseid)::text)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on alpubdb.billrecord (cost=0.00..13,860.31 rows=37,084 width=33) (never executed)

  • 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
  • Filter: ((billrecord.billstate = 0) AND ((billrecord.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text))
15. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.12..0.12 rows=6 width=98) (never executed)

  • Output: keys_1.houseid
16. 0.000 0.000 ↓ 0.0 0

CTE Scan on keys keys_1 (cost=0.00..0.12 rows=6 width=98) (never executed)

  • Output: keys_1.houseid
17. 0.000 21,115.008 ↓ 0.0 0 1

Nested Loop (cost=1,000.00..161,142.97 rows=1 width=20) (actual time=21,115.008..21,115.008 rows=0 loops=1)

  • Output: opendoorrecord.opentime
  • Join Filter: ((opendoorrecord.lockid)::text = _.lockid)
18. 43.485 21,115.102 ↓ 0.0 0 1

Gather (cost=1,000.00..161,142.85 rows=1 width=39) (actual time=21,115.005..21,115.102 rows=0 loops=1)

  • Output: opendoorrecord.opentime, opendoorrecord.lockid
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 4
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 0.948 ms, Inlining 0.000 ms, Optimization 0.640 ms, Emission 6.520 ms, Total 8.108 ms
  • JIT for worker 1:
  • Functions: 4
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 0.762 ms, Inlining 0.000 ms, Optimization 0.540 ms, Emission 7.592 ms, Total 8.894 ms
19. 21,071.617 21,071.617 ↓ 0.0 0 3 / 3

Parallel Seq Scan on alpubdb.opendoorrecord (cost=0.00..160,142.75 rows=1 width=39) (actual time=21,071.617..21,071.617 rows=0 loops=3)

  • Output: opendoorrecord.opentime, opendoorrecord.lockid
  • Filter: (((opendoorrecord.userid)::text = '13800138000'::text) AND ((opendoorrecord.lockid)::text = ANY ('{AILOCK_1c43bbf2f8f0,AILOCK_391c60ed0418,AILOCK_68fd52b06f80,AILOCK_e3790ff98100,AILOCK_f62abbf2f8f0}'::text[])))
  • Rows Removed by Filter: 5336271
  • Worker 0: actual time=21067.055..21067.055 rows=0 loops=1
  • Worker 1: actual time=21042.300..21042.300 rows=0 loops=1
20. 0.000 0.000 ↓ 0.0 0

Function Scan on pg_catalog.unnest _ (cost=0.00..0.05 rows=5 width=32) (never executed)

  • Output: _.lockid
  • Function Call: unnest('{AILOCK_1c43bbf2f8f0,AILOCK_391c60ed0418,AILOCK_68fd52b06f80,AILOCK_e3790ff98100,AILOCK_f62abbf2f8f0}'::text[])
Execution time : 23,092.459 ms