explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gaCc

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 98.467 ↑ 1.0 1 1

Aggregate (cost=460.75..460.76 rows=1 width=32) (actual time=98.466..98.467 rows=1 loops=1)

  • Output: CASE WHEN (NOT $1) THEN NULL::text WHEN $2 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.022 63.457 ↓ 7.5 15 1

Append (cost=100.00..229.50 rows=2 width=254) (actual time=54.149..63.457 rows=15 loops=1)

4. 54.149 54.149 ↑ 1.0 1 1

Foreign Scan (cost=100.00..114.74 rows=1 width=254) (actual time=54.147..54.149 rows=1 loops=1)

  • Output: lockkeyinfo.houseid, lockkeyinfo.lockid, COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount)
  • Relations: (fdw_ailock_business_alpub.lockkeyinfo) INNER JOIN (fdw_ailock_business_alpub.houseinfo)
  • Remote SQL: SELECT r1.houseid, r1.lockid, r1.keyholder, r1.useraccount FROM (alpubdb.lockkeyinfo r1 INNER JOIN alpubdb.houseinfo r2 ON (((r1.houseid = r2.houseid)) AND ((r2.xzqhdm ~~ '330522%'::text)) AND ((('18865552222'::text = r1.keyholder) OR ('18865552222'::text = r1.useraccount))) AND ((r1.userrole <> 255))))
5. 9.286 9.286 ↓ 14.0 14 1

Foreign Scan (cost=100.00..114.74 rows=1 width=254) (actual time=9.276..9.286 rows=14 loops=1)

  • Output: keyhistoryinfo.houseid, keyhistoryinfo.lockid, COALESCE(keyhistoryinfo.keyholder, keyhistoryinfo.useraccount)
  • Relations: (fdw_ailock_business_alpub.keyhistoryinfo) INNER JOIN (fdw_ailock_business_alpub.houseinfo)
  • Remote SQL: SELECT r1.houseid, r1.lockid, r1.keyholder, r1.useraccount FROM (alpubdb.keyhistoryinfo r1 INNER JOIN alpubdb.houseinfo r2 ON (((r1.houseid = r2.houseid)) AND ((r2.xzqhdm ~~ '330522%'::text)) AND ((('18865552222'::text = r1.keyholder) OR ('18865552222'::text = r1.useraccount))) AND ((r1.userrole <> 255))))
6.          

Initplan (for Aggregate)

7. 54.154 54.154 ↑ 2.0 1 1

CTE Scan on keys (cost=0.00..0.04 rows=2 width=0) (actual time=54.153..54.154 rows=1 loops=1)

8. 9.950 42.696 ↓ 0.0 0 1

Nested Loop (cost=100.00..121.25 rows=1 width=0) (actual time=42.695..42.696 rows=0 loops=1)

  • Join Filter: ((keys_1.houseid)::text = (billrecord.houseid)::text)
  • Rows Removed by Join Filter: 13965
9. 11.333 11.333 ↓ 931.0 931 1

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..121.19 rows=1 width=98) (actual time=1.672..11.333 rows=931 loops=1)

  • 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.servicetype, billrecord.paytype, billrecord.billendtime, 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.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text)
  • Rows Removed by Filter: 34
  • Remote SQL: SELECT houseid, billcreatetime FROM alpubdb.billrecord WHERE ((billstate = 0))
10. 21.413 21.413 ↓ 7.5 15 931

CTE Scan on keys keys_1 (cost=0.00..0.04 rows=2 width=98) (actual time=0.001..0.023 rows=15 loops=931)

  • Output: keys_1.houseid, keys_1.lockid, keys_1.userid
11. 0.079 1.559 ↓ 12.0 12 1

Nested Loop (cost=100.00..109.93 rows=1 width=58) (actual time=1.398..1.559 rows=12 loops=1)

  • Output: opendoorrecord.opentime
  • Join Filter: ((opendoorrecord.lockid)::text = "*VALUES*".column1)
  • Rows Removed by Join Filter: 78
12. 1.396 1.396 ↓ 6.0 6 1

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=100.00..109.55 rows=1 width=156) (actual time=1.391..1.396 rows=6 loops=1)

  • Output: opendoorrecord.id, opendoorrecord.lockid, opendoorrecord.userid, opendoorrecord.openmode, opendoorrecord.opentime, opendoorrecord.openresult, opendoorrecord.uploadmode, opendoorrecord.lockpower, opendoorrecord.opendirection, opendoorrecord.uploadtime, opendoorrecord.houseid, opendoorrecord.deleteflag
  • Remote SQL: SELECT lockid, opentime FROM alpubdb.opendoorrecord WHERE ((userid = '18865552222'::text)) AND ((lockid = ANY ('{AILOCK_1c43bbf2f8f0,AILOCK_391c60ed0418,AILOCK_68fd52b06f80,AILOCK_e3790ff98100,AILOCK_f62abbf2f8f0}'::text[])))
13. 0.084 0.084 ↑ 1.0 15 6

Values Scan on "*VALUES*" (cost=0.00..0.19 rows=15 width=32) (actual time=0.001..0.014 rows=15 loops=6)

  • Output: "*VALUES*".column1
Planning time : 0.562 ms
Execution time : 101.657 ms