explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vzj0

Settings
# exclusive inclusive rows x rows loops node
1. 0.098 24,255.289 ↑ 1.0 1 1

Aggregate (cost=516.62..516.64 rows=1 width=32) (actual time=24,255.288..24,255.289 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.003 2,381.784 ↓ 0.0 0 1

Append (cost=100.00..265.50 rows=2 width=254) (actual time=2,381.784..2,381.784 rows=0 loops=1)

4. 1,613.249 1,613.249 ↓ 0.0 0 1

Foreign Scan (cost=100.00..132.73 rows=1 width=254) (actual time=1,613.248..1,613.249 rows=0 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 ((('13800138000'::text = r1.keyholder) OR ('13800138000'::text = r1.useraccount))) AND ((r1.userrole <> 255))))
5. 768.532 768.532 ↓ 0.0 0 1

Foreign Scan (cost=100.00..132.73 rows=1 width=254) (actual time=768.532..768.532 rows=0 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 ((('13800138000'::text = r1.keyholder) OR ('13800138000'::text = r1.useraccount))) AND ((r1.userrole <> 255))))
6.          

Initplan (for Aggregate)

7. 2,381.786 2,381.786 ↓ 0.0 0 1

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

8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=100.00..130.25 rows=1 width=0) (never executed)

  • Join Filter: ((keys_1.houseid)::text = (billrecord.houseid)::text)
9. 0.000 0.000 ↓ 0.0 0

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..130.19 rows=1 width=98) (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.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text)
  • Remote SQL: SELECT houseid, billcreatetime FROM alpubdb.billrecord WHERE ((billstate = 0))
10. 0.000 0.000 ↓ 0.0 0

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

  • Output: keys_1.houseid, keys_1.lockid, keys_1.userid
11. 0.001 21,873.405 ↓ 0.0 0 1

Nested Loop (cost=100.00..120.80 rows=1 width=58) (actual time=21,873.404..21,873.405 rows=0 loops=1)

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

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=100.00..118.55 rows=1 width=156) (actual time=21,873.403..21,873.404 rows=0 loops=1)

  • Output: opendoorrecord.id, opendoorrecord.lockid, opendoorrecord.userid, opendoorrecord.openmode, opendoorrecord.opentime, opendoorrecord.openresult, opendoorrecord.uploadmode, opendoorrecord.lockpower, opendoorrecord.opendirection, opendoorrecord.uploadtime, opendoorrecord.deleteflag, opendoorrecord.houseid
  • Remote SQL: SELECT lockid, opentime FROM alpubdb.opendoorrecord WHERE ((userid = '13800138000'::text)) AND ((lockid = ANY ('{AILOCK_1c43bbf2f8f0,AILOCK_391c60ed0418,AILOCK_68fd52b06f80,AILOCK_e3790ff98100,AILOCK_f62abbf2f8f0}'::text[])))
13. 0.000 0.000 ↓ 0.0 0

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

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