explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fbaA

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

GroupAggregate (cost=1,760.31..1,761.09 rows=6 width=68) (actual time=26,370.514..26,395.587 rows=5,028 loops=1)

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

Sort (cost=1,760.31..1,760.34 rows=12 width=124) (actual time=26,370.491..26,375.223 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. 22.438 26,351.396 ↓ 559.2 6,710 1

Nested Loop Left Join (cost=448.41..1,760.09 rows=12 width=124) (actual time=168.200..26,351.396 rows=6,710 loops=1)

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

Hash Right Join (cost=329.89..337.60 rows=6 width=218) (actual time=165.175..188.357 rows=6,701 loops=1)

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

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..107.70 rows=1 width=160) (actual time=5.962..15.233 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
  • Remote SQL: SELECT lockid, billcreatetime, billstate FROM alpubdb.billrecord WHERE ((billcreatetime < '2020-04-23'::text)) AND ((billstate = 0))
6. 4.050 159.202 ↓ 859.5 5,157 1

Hash (cost=229.81..229.81 rows=6 width=156) (actual time=159.201..159.202 rows=5,157 loops=1)

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

Subquery Scan on keys (cost=229.69..229.81 rows=6 width=156) (actual time=144.618..155.152 rows=5,157 loops=1)

  • Output: keys.keyholder, keys.lockid
8. 11.142 148.692 ↓ 859.5 5,157 1

HashAggregate (cost=229.69..229.75 rows=6 width=254) (actual time=144.616..148.692 rows=5,157 loops=1)

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

Append (cost=100.00..229.65 rows=6 width=254) (actual time=6.889..137.550 rows=5,965 loops=1)

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

Foreign Scan (cost=100.00..114.78 rows=3 width=254) (actual time=6.887..102.936 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 ((r2.xzqhdm ~~ '330522%'::text)) AND ((r1.userrole <> 255))))
11. 26.682 26.682 ↓ 452.3 1,357 1

Foreign Scan (cost=100.00..114.78 rows=3 width=254) (actual time=9.214..26.682 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 ((r2.xzqhdm ~~ '330522%'::text)) AND ((r1.userrole <> 255))))
12. 26.804 26,140.601 ↓ 0.0 0 6,701

Append (cost=118.52..237.06 rows=2 width=4) (actual time=3.900..3.901 rows=0 loops=6,701)

13. 13.402 13,033.445 ↓ 0.0 0 6,701

Limit (cost=118.52..118.53 rows=1 width=4) (actual time=1.945..1.945 rows=0 loops=6,701)

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

Unique (cost=118.52..118.53 rows=1 width=4) (actual time=1.943..1.943 rows=0 loops=6,701)

  • Output: ((opendoorrecord.opentime)::date)
15. 3,966.992 13,006.641 ↓ 0.0 0 6,701

Sort (cost=118.52..118.52 rows=1 width=4) (actual time=1.941..1.941 rows=0 loops=6,701)

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

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

  • Output: (opendoorrecord.opentime)::date
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((opentime >= '2020-04-23'::text)) AND ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
17. 3,953.590 13,080.352 ↓ 0.0 0 6,701

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

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

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

  • Output: (opendoorrecord_1.opentime)::date
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((opentime < '2020-02-23'::text)) AND ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
Planning time : 0.711 ms
Execution time : 26,404.421 ms