explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJNo

Settings
# exclusive inclusive rows x rows loops node
1. 19.245 26,135.681 ↓ 838.0 5,028 1

GroupAggregate (cost=1,952.11..1,952.86 rows=6 width=82) (actual time=26,111.816..26,135.681 rows=5,028 loops=1)

  • Output: keys.keyholder, count(*) FILTER (WHERE (((opendoorrecord.opentime)::date) < ((timezone('PRC'::text, now()) - '90 days'::interval))::date)), count(*) FILTER (WHERE (((opendoorrecord.opentime)::date) > ((timezone('PRC'::text, now()) - '30 days'::interval))::date)), count(*) FILTER (WHERE ((billrecord.billstate = 0) AND ((billrecord.billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text)))
  • Group Key: keys.keyholder
2. 25.057 26,116.436 ↓ 559.2 6,710 1

Sort (cost=1,952.11..1,952.14 rows=12 width=124) (actual time=26,111.791..26,116.436 rows=6,710 loops=1)

  • Output: keys.keyholder, ((opendoorrecord.opentime)::date), billrecord.billstate, billrecord.billcreatetime
  • Sort Key: keys.keyholder
  • Sort Method: quicksort Memory: 586kB
3. 23.630 26,091.379 ↓ 559.2 6,710 1

Nested Loop Left Join (cost=468.22..1,951.90 rows=12 width=124) (actual time=316.788..26,091.379 rows=6,710 loops=1)

  • Output: keys.keyholder, ((opendoorrecord.opentime)::date), billrecord.billstate, billrecord.billcreatetime
4. 13.478 342.610 ↓ 1,116.8 6,701 1

Hash Right Join (cost=334.68..349.10 rows=6 width=218) (actual time=311.684..342.610 rows=6,701 loops=1)

  • Output: keys.keyholder, keys.lockid, billrecord.billstate, billrecord.billcreatetime
  • Hash Cond: ((billrecord.lockid)::text = (keys.lockid)::text)
5. 19.513 19.513 ↓ 961.0 961 1

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..114.41 rows=1 width=160) (actual time=2.043..19.513 rows=961 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: 259
  • Remote SQL: SELECT lockid, billcreatetime, billstate FROM alpubdb.billrecord WHERE ((billstate = 0))
6. 4.189 309.619 ↓ 859.5 5,157 1

Hash (cost=234.61..234.61 rows=6 width=156) (actual time=309.619..309.619 rows=5,157 loops=1)

  • Output: keys.keyholder, keys.lockid
  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 388kB
7. 6.495 305.430 ↓ 859.5 5,157 1

Subquery Scan on keys (cost=234.49..234.61 rows=6 width=156) (actual time=294.685..305.430 rows=5,157 loops=1)

  • Output: keys.keyholder, keys.lockid
8. 11.387 298.935 ↓ 859.5 5,157 1

HashAggregate (cost=234.49..234.55 rows=6 width=254) (actual time=294.682..298.935 rows=5,157 loops=1)

  • Output: lockkeyinfo.lockid, lockkeyinfo.keyholder, houseinfo.xzqhdm
  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder, houseinfo.xzqhdm
9. 7.728 287.548 ↓ 994.2 5,965 1

Append (cost=100.00..234.44 rows=6 width=254) (actual time=33.245..287.548 rows=5,965 loops=1)

10. 251.475 251.475 ↓ 1,536.0 4,608 1

Foreign Scan (cost=100.00..117.18 rows=3 width=254) (actual time=33.242..251.475 rows=4,608 loops=1)

  • Output: lockkeyinfo.lockid, lockkeyinfo.keyholder, houseinfo.xzqhdm
  • Relations: (fdw_ailock_business_alpub.lockkeyinfo) INNER JOIN (fdw_ailock_business_alpub.houseinfo)
  • Remote SQL: SELECT r1.lockid, r1.keyholder, r2.xzqhdm FROM (alpubdb.lockkeyinfo r1 INNER JOIN alpubdb.houseinfo r2 ON (((r1.houseid = r2.houseid)) AND (("left"(r2.xzqhdm, 6) = '330522'::text)) AND ((r1.userrole <> 255))))
11. 28.345 28.345 ↓ 452.3 1,357 1

Foreign Scan (cost=100.00..117.18 rows=3 width=254) (actual time=10.151..28.345 rows=1,357 loops=1)

  • Output: keyhistoryinfo.lockid, keyhistoryinfo.keyholder, houseinfo_1.xzqhdm
  • Relations: (fdw_ailock_business_alpub.keyhistoryinfo) INNER JOIN (fdw_ailock_business_alpub.houseinfo)
  • Remote SQL: SELECT r1.lockid, r1.keyholder, r2.xzqhdm FROM (alpubdb.keyhistoryinfo r1 INNER JOIN alpubdb.houseinfo r2 ON (((r1.houseid = r2.houseid)) AND (("left"(r2.xzqhdm, 6) = '330522'::text)) AND ((r1.userrole <> 255))))
12. 26.804 25,725.139 ↓ 0.0 0 6,701

Append (cost=133.54..267.11 rows=2 width=4) (actual time=3.838..3.839 rows=0 loops=6,701)

13. 13.402 12,812.312 ↓ 0.0 0 6,701

Limit (cost=133.54..133.55 rows=1 width=4) (actual time=1.912..1.912 rows=0 loops=6,701)

  • Output: ((opendoorrecord.opentime)::date)
14. 13.402 12,798.910 ↓ 0.0 0 6,701

Unique (cost=133.54..133.55 rows=1 width=4) (actual time=1.910..1.910 rows=0 loops=6,701)

  • Output: ((opendoorrecord.opentime)::date)
15. 3,926.786 12,785.508 ↓ 0.0 0 6,701

Sort (cost=133.54..133.55 rows=1 width=4) (actual time=1.908..1.908 rows=0 loops=6,701)

  • Output: ((opendoorrecord.opentime)::date)
  • Sort Key: ((opendoorrecord.opentime)::date)
  • Sort Method: quicksort Memory: 25kB
16. 8,858.722 8,858.722 ↓ 0.0 0 6,701

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=100.00..133.53 rows=1 width=4) (actual time=1.322..1.322 rows=0 loops=6,701)

  • Output: (opendoorrecord.opentime)::date
  • Filter: ((opendoorrecord.opentime)::text > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text)
  • Rows Removed by Filter: 0
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
17. 3,906.683 12,886.023 ↓ 0.0 0 6,701

Limit (cost=100.00..133.53 rows=1 width=4) (actual time=1.923..1.923 rows=0 loops=6,701)

  • Output: ((opendoorrecord_1.opentime)::date)
18. 8,979.340 8,979.340 ↓ 0.0 0 6,701

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord opendoorrecord_1 (cost=100.00..133.53 rows=1 width=4) (actual time=1.340..1.340 rows=0 loops=6,701)

  • Output: (opendoorrecord_1.opentime)::date
  • Filter: ((opendoorrecord_1.opentime)::text < (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
  • Rows Removed by Filter: 0
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
Planning time : 0.656 ms
Execution time : 26,439.967 ms