explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ORo59

Settings
# exclusive inclusive rows x rows loops node
1. 0.071 0.196 ↑ 1.0 1 1

Aggregate (cost=3.41..3.42 rows=1 width=32) (actual time=0.196..0.196 rows=1 loops=1)

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

Initplan (for Aggregate)

3. 0.002 0.002 ↑ 2.0 1 1

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

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

Nested Loop (cost=0.42..3.29 rows=2 width=0) (actual time=0.037..0.037 rows=0 loops=1)

5. 0.004 0.004 ↑ 1.0 2 1

Function Scan on pg_catalog.unnest keys_2 (cost=0.00..0.02 rows=2 width=32) (actual time=0.003..0.004 rows=2 loops=1)

  • Output: keys_2.lockid
  • Function Call: unnest('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[])
6. 0.032 0.032 ↓ 0.0 0 2

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

  • 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)
  • Filter: ((billrecord.billstate = 0) AND ((billrecord.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text))
  • Rows Removed by Filter: 9
7. 0.031 0.086 ↓ 25.0 25 1

Nested Loop (cost=0.56..1.50 rows=1 width=20) (actual time=0.032..0.086 rows=25 loops=1)

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

Index Scan using opendoorrecord_userid_lockid_idx on alpubdb.opendoorrecord (cost=0.56..1.45 rows=1 width=39) (actual time=0.023..0.055 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
  • Index Cond: (((opendoorrecord.userid)::text = '13099432885'::text) AND ((opendoorrecord.lockid)::text = ANY ('{AILOCK_142b9de364c4,AILOCK_67ffcd32daa4}'::text[])))
9. 0.000 0.000 ↑ 1.0 2 25

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

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