explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dE8h

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 380.399 ↑ 1.0 1 1

Aggregate (cost=251.60..251.61 rows=1 width=32) (actual time=380.398..380.399 rows=1 loops=1)

  • Output: CASE WHEN (NOT $0) THEN NULL::text WHEN $1 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.          

Initplan (for Aggregate)

3. 0.002 0.002 ↑ 100.0 1 1

Function Scan on pg_catalog.unnest keys_1 (cost=0.00..1.00 rows=100 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • Function Call: unnest('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[])
4. 26.571 379.571 ↓ 0.0 0 1

Nested Loop (cost=100.00..132.44 rows=1 width=0) (actual time=379.570..379.571 rows=0 loops=1)

  • Join Filter: (keys_2.lockid = (billrecord.lockid)::text)
  • Rows Removed by Join Filter: 66052
5. 353.000 353.000 ↓ 33,026.0 33,026 1

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..130.19 rows=1 width=98) (actual time=1.254..353.000 rows=33,026 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.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)
  • Rows Removed by Filter: 7169
  • Remote SQL: SELECT lockid, billcreatetime FROM alpubdb.billrecord WHERE ((billstate = 0))
6. 0.000 0.000 ↑ 50.0 2 33,026

Function Scan on pg_catalog.unnest keys_2 (cost=0.00..1.00 rows=100 width=32) (actual time=0.000..0.000 rows=2 loops=33,026)

  • Output: keys_2.lockid
  • Function Call: unnest('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[])
7. 0.009 0.746 ↓ 25.0 25 1

Nested Loop (cost=100.00..119.10 rows=1 width=58) (actual time=0.719..0.746 rows=25 loops=1)

  • Output: opendoorrecord.opentime
  • Join Filter: ((opendoorrecord.lockid)::text = keys.lockid)
  • Rows Removed by Join Filter: 25
8. 0.712 0.712 ↓ 25.0 25 1

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=100.00..116.85 rows=1 width=156) (actual time=0.710..0.712 rows=25 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 ((lockid = ANY ('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[]))) AND ((userid = '13099432885'::text))
9. 0.025 0.025 ↑ 50.0 2 25

Function Scan on pg_catalog.unnest keys (cost=0.00..1.00 rows=100 width=32) (actual time=0.000..0.001 rows=2 loops=25)

  • Output: keys.lockid
  • Function Call: unnest('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[])
Planning time : 0.271 ms
Execution time : 381.483 ms