explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iude

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 212.110 ↑ 48.0 3 1

Subquery Scan on criteria (cost=1,010,502,120,886.48..1,010,502,120,919.24 rows=144 width=115) (actual time=211.964..212.110 rows=3 loops=1)

  • Output: CASE WHEN criteria.has_overdue_bills THEN 'red'::text ELSE CASE WHEN (criteria.has_sufficient_stay AND criteria.has_normal_usage) THEN 'green'::text ELSE 'yellow'::text END END, criteria.account, criteria.division, criteria.division_detail, criteria.has_sufficient_stay, criteria.has_normal_usage, criteria.has_overdue_bills, criteria.active_from
2. 0.165 212.106 ↑ 48.0 3 1

GroupAggregate (cost=1,010,502,120,886.48..1,010,502,120,917.80 rows=144 width=83) (actual time=211.961..212.106 rows=3 loops=1)

  • Output: ""*SELECT* 1"".keyholder, (""left""((""*SELECT* 1"".xzqhdm)::text, 6)), array_agg(DISTINCT ""*SELECT* 1"".xzqhdm), (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))) > 0), min(((opendoorrecord.opentime)::date))
  • Group Key: ""*SELECT* 1"".keyholder, (""left""((""*SELECT* 1"".xzqhdm)::text, 6))
3. 0.080 211.941 ↑ 5.4 80 1

Sort (cost=1,010,502,120,886.48..1,010,502,120,887.56 rows=432 width=84) (actual time=211.936..211.941 rows=80 loops=1)

  • Output: ""*SELECT* 1"".keyholder, (""left""((""*SELECT* 1"".xzqhdm)::text, 6)), ""*SELECT* 1"".xzqhdm, ((opendoorrecord.opentime)::date), billrecord.billstate, billrecord.billcreatetime
  • Sort Key: (""left""((""*SELECT* 1"".xzqhdm)::text, 6))
  • Sort Method: quicksort Memory: 28kB
4. 0.163 211.861 ↑ 5.4 80 1

Nested Loop Left Join (cost=4,001,279,632.06..1,010,502,120,867.57 rows=432 width=84) (actual time=5.990..211.861 rows=80 loops=1)

  • Output: ""*SELECT* 1"".keyholder, ""left""((""*SELECT* 1"".xzqhdm)::text, 6), ""*SELECT* 1"".xzqhdm, ((opendoorrecord.opentime)::date), billrecord.billstate, billrecord.billcreatetime
5. 0.110 147.938 ↑ 5.4 80 1

Nested Loop Left Join (cost=3,001,279,632.06..578,502,090,726.93 rows=432 width=52) (actual time=5.161..147.938 rows=80 loops=1)

  • Output: ""*SELECT* 1"".keyholder, ""*SELECT* 1"".xzqhdm, billrecord.billstate, billrecord.billcreatetime, ((opendoorrecord.opentime)::date)
6. 0.093 7.294 ↑ 2.5 58 1

Nested Loop Left Join (cost=2,000,000,000.00..146,009,748,750.57 rows=144 width=67) (actual time=3.444..7.294 rows=58 loops=1)

  • Output: ""*SELECT* 1"".keyholder, ""*SELECT* 1"".xzqhdm, ""*SELECT* 1"".lockid, billrecord.billstate, billrecord.billcreatetime
7. 0.014 4.533 ↑ 2.5 58 1

Append (cost=1,000,000,000.00..2,000,187,839.97 rows=144 width=47) (actual time=2.373..4.533 rows=58 loops=1)

8. 0.004 2.375 ↓ 2.0 4 1

Subquery Scan on "*SELECT* 1" (cost=1,000,000,000.00..1,000,023,889.66 rows=2 width=47) (actual time=2.372..2.375 rows=4 loops=1)

  • Output: ""*SELECT* 1"".keyholder, ""*SELECT* 1"".xzqhdm, ""*SELECT* 1"".lockid, ""*SELECT* 1"".keystate
9. 2.371 2.371 ↓ 2.0 4 1

Foreign Scan (cost=1,000,000,000.00..1,000,023,889.64 rows=2 width=47) (actual time=2.370..2.371 rows=4 loops=1)

  • Output: lockkeyinfo.lockid, lockkeyinfo.keyholder, lockkeyinfo.keystate, houseinfo.xzqhdm
  • Relations: (fdw_ailock_business_alpub.lockkeyinfo) INNER JOIN (fdw_ailock_business_alpub.houseinfo)
  • Remote SQL: SELECT r1.lockid, r1.keyholder, r1.keystate, r2.xzqhdm FROM (alpubdb.lockkeyinfo r1 INNER JOIN alpubdb.houseinfo r2 ON (((r1.houseid = r2.houseid)) AND ((r1.userrole <> 255)) AND ((r1.keyholder = '18865558087'::text))))
10. 0.021 2.144 ↑ 2.6 54 1

Subquery Scan on "*SELECT* 2" (cost=1,000,000,000.00..1,000,163,949.59 rows=142 width=47) (actual time=2.095..2.144 rows=54 loops=1)

  • Output: ""*SELECT* 2"".keyholder, ""*SELECT* 2"".xzqhdm, ""*SELECT* 2"".lockid, ""*SELECT* 2"".""?column?"
11. 2.123 2.123 ↑ 2.6 54 1

Foreign Scan (cost=1,000,000,000.00..1,000,163,948.17 rows=142 width=47) (actual time=2.094..2.123 rows=54 loops=1)

  • Output: keyhistoryinfo.lockid, keyhistoryinfo.keyholder, 1, 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 ((r1.userrole <> 255)) AND ((r1.keyholder = '18865558087'::text))))
12. 0.000 2.668 ↓ 0.0 0 58

Limit (cost=1,000,000,000.00..1,000,066,395.19 rows=1 width=24) (actual time=0.046..0.046 rows=0 loops=58)

  • Output: billrecord.billstate, billrecord.billcreatetime
13. 0.445 2.668 ↓ 0.0 0 58

Result (cost=1,000,000,000.00..1,000,066,395.19 rows=1 width=24) (actual time=0.046..0.046 rows=0 loops=58)

  • Output: billrecord.billstate, billrecord.billcreatetime
  • One-Time Filter: (""*SELECT* 1"".keystate = 0)
14. 2.223 2.223 ↓ 0.0 0 3

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=1,000,000,000.00..1,000,066,395.19 rows=1 width=24) (actual time=0.741..0.741 rows=0 loops=3)

  • 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: 0
  • Remote SQL: SELECT billcreatetime, billstate FROM alpubdb.billrecord WHERE ((lockid = $1::character varying(40))) AND ((billstate = 0))
15. 0.116 140.534 ↑ 3.0 1 58

Append (cost=1,001,279,632.06..3,003,419,041.47 rows=3 width=4) (actual time=2.096..2.423 rows=1 loops=58)

16. 0.058 46.980 ↓ 0.0 0 58

Limit (cost=1,001,279,632.06..1,001,279,632.07 rows=1 width=4) (actual time=0.810..0.810 rows=0 loops=58)

  • Output: ((opendoorrecord.opentime)::date)
17. 0.058 46.922 ↓ 0.0 0 58

Unique (cost=1,001,279,632.06..1,001,279,632.07 rows=1 width=4) (actual time=0.809..0.809 rows=0 loops=58)

  • Output: ((opendoorrecord.opentime)::date)
18. 11.832 46.864 ↓ 0.0 0 58

Sort (cost=1,001,279,632.06..1,001,279,632.06 rows=1 width=4) (actual time=0.808..0.808 rows=0 loops=58)

  • Output: ((opendoorrecord.opentime)::date)
  • Sort Key: ((opendoorrecord.opentime)::date)
  • Sort Method: quicksort Memory: 25kB
19. 35.032 35.032 ↓ 0.0 0 58

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=1,000,000,000.00..1,001,279,632.05 rows=1 width=4) (actual time=0.604..0.604 rows=0 loops=58)

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

Limit (cost=1,000,000,000.00..1,001,279,631.97 rows=1 width=4) (actual time=0.791..0.791 rows=0 loops=58)

  • Output: ((opendoorrecord_1.opentime)::date)
21. 34.104 34.104 ↓ 0.0 0 58

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord opendoorrecord_1 (cost=1,000,000,000.00..1,001,279,631.97 rows=1 width=4) (actual time=0.588..0.588 rows=0 loops=58)

  • Output: (opendoorrecord_1.opentime)::date
  • Filter: ((opendoorrecord_1.opentime)::text < (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
  • Rows Removed by Filter: 2
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
22. 0.058 47.560 ↓ 0.0 0 58

Limit (cost=1,000,859,777.38..1,000,859,777.39 rows=1 width=4) (actual time=0.820..0.820 rows=0 loops=58)

  • Output: ((opendoorrecord_2.opentime)::date)
23. 12.064 47.502 ↓ 0.0 0 58

Sort (cost=1,000,859,777.38..1,000,859,777.39 rows=1 width=4) (actual time=0.819..0.819 rows=0 loops=58)

  • Output: ((opendoorrecord_2.opentime)::date)
  • Sort Key: ((opendoorrecord_2.opentime)::date)
  • Sort Method: top-N heapsort Memory: 25kB
24. 35.438 35.438 ↓ 14.0 14 58

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord opendoorrecord_2 (cost=1,000,000,000.00..1,000,859,777.38 rows=1 width=4) (actual time=0.601..0.611 rows=14 loops=58)

  • Output: (opendoorrecord_2.opentime)::date
  • Remote SQL: SELECT opentime FROM alpubdb.opendoorrecord WHERE ((userid = $1::character varying(20))) AND ((lockid = $2::character varying(40)))
25. 16.640 63.760 ↑ 1.0 1 80

Limit (cost=1,000,000,000.00..1,000,000,069.75 rows=1 width=4) (actual time=0.797..0.797 rows=1 loops=80)

  • Output: NULL::integer
26. 47.120 47.120 ↑ 1.0 1 80

Foreign Scan on fdw_ailock_business_alpub.appuseraccount (cost=1,000,000,000.00..1,000,000,069.75 rows=1 width=4) (actual time=0.589..0.589 rows=1 loops=80)

  • Output: NULL::integer
  • Remote SQL: SELECT NULL FROM alpubdb.appuseraccount WHERE ((headpicture IS NOT NULL)) AND ((account = $1::character varying(20))) AND ((length(userid) = 18))
Planning time : 0.919 ms
Execution time : 227.495 ms