explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ndG

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 493.075 ↑ 47.7 3 1

Subquery Scan on criteria (cost=409,475,410.52..409,475,443.06 rows=143 width=115) (actual time=492.929..493.075 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.178 493.071 ↑ 47.7 3 1

GroupAggregate (cost=409,475,410.52..409,475,441.63 rows=143 width=83) (actual time=492.927..493.071 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.087 492.893 ↑ 5.4 80 1

Sort (cost=409,475,410.52..409,475,411.60 rows=429 width=84) (actual time=492.887..492.893 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.199 492.806 ↑ 5.4 80 1

Nested Loop Left Join (cost=1,048,840.90..409,475,391.77 rows=429 width=84) (actual time=3.710..492.806 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.113 430.607 ↑ 5.4 80 1

Nested Loop Left Join (cost=1,048,740.90..409,402,560.44 rows=429 width=52) (actual time=3.164..430.607 rows=80 loops=1)

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

Nested Loop Left Join (cost=200.00..8,816,073.78 rows=143 width=67) (actual time=1.686..286.828 rows=58 loops=1)

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

Append (cost=100.00..56,696.72 rows=143 width=47) (actual time=0.975..284.436 rows=58 loops=1)

8. 0.004 0.978 ↓ 2.0 4 1

Subquery Scan on "*SELECT* 1" (cost=100.00..24,588.47 rows=2 width=47) (actual time=0.975..0.978 rows=4 loops=1)

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

Foreign Scan (cost=100.00..24,588.45 rows=2 width=47) (actual time=0.973..0.974 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.027 283.437 ↑ 2.6 54 1

Subquery Scan on "*SELECT* 2" (cost=14,312.20..32,107.54 rows=141 width=47) (actual time=270.626..283.437 rows=54 loops=1)

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

Hash Join (cost=14,312.20..32,106.13 rows=141 width=47) (actual time=270.625..283.410 rows=54 loops=1)

  • Output: keyhistoryinfo.lockid, keyhistoryinfo.keyholder, 1, houseinfo_1.xzqhdm
  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
12. 0.922 0.922 ↑ 2.0 71 1

Foreign Scan on fdw_ailock_business_alpub.keyhistoryinfo (cost=100.00..16,784.98 rows=142 width=63) (actual time=0.897..0.922 rows=71 loops=1)

  • Output: keyhistoryinfo.lockid, keyhistoryinfo.keyholder, keyhistoryinfo.houseid
  • Remote SQL: SELECT lockid, keyholder, houseid FROM alpubdb.keyhistoryinfo WHERE ((userrole <> 255)) AND ((keyholder = '18865558087'::text))
13. 44.593 269.474 ↓ 1.0 126,757 1

Hash (cost=11,541.20..11,541.20 rows=125,440 width=47) (actual time=269.474..269.474 rows=126,757 loops=1)

  • Output: houseinfo_1.xzqhdm, houseinfo_1.houseid
  • Buckets: 65,536 Batches: 4 Memory Usage: 3,005kB
14. 224.881 224.881 ↓ 1.0 126,757 1

Foreign Scan on fdw_ailock_business_alpub.houseinfo houseinfo_1 (cost=100.00..11,541.20 rows=125,440 width=47) (actual time=83.394..224.881 rows=126,757 loops=1)

  • Output: houseinfo_1.xzqhdm, houseinfo_1.houseid
  • Remote SQL: SELECT houseid, xzqhdm FROM alpubdb.houseinfo
15. 0.000 2.262 ↓ 0.0 0 58

Limit (cost=100.00..61,254.37 rows=1 width=24) (actual time=0.039..0.039 rows=0 loops=58)

  • Output: billrecord.billstate, billrecord.billcreatetime
16. 0.462 2.262 ↓ 0.0 0 58

Result (cost=100.00..61,254.37 rows=1 width=24) (actual time=0.039..0.039 rows=0 loops=58)

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

Foreign Scan on fdw_ailock_business_alpub.billrecord (cost=100.00..61,254.37 rows=1 width=24) (actual time=0.600..0.600 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))
18. 0.174 143.666 ↑ 3.0 1 58

Append (cost=1,048,540.90..2,801,304.07 rows=3 width=4) (actual time=2.146..2.477 rows=1 loops=58)

19. 0.000 49.358 ↓ 0.0 0 58

Limit (cost=1,048,540.90..1,048,540.90 rows=1 width=4) (actual time=0.851..0.851 rows=0 loops=58)

  • Output: ((opendoorrecord.opentime)::date)
20. 0.116 49.358 ↓ 0.0 0 58

Unique (cost=1,048,540.90..1,048,540.90 rows=1 width=4) (actual time=0.851..0.851 rows=0 loops=58)

  • Output: ((opendoorrecord.opentime)::date)
21. 13.688 49.242 ↓ 0.0 0 58

Sort (cost=1,048,540.90..1,048,540.90 rows=1 width=4) (actual time=0.849..0.849 rows=0 loops=58)

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

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord (cost=100.00..1,048,540.89 rows=1 width=4) (actual time=0.613..0.613 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)))
23. 11.484 46.168 ↓ 0.0 0 58

Limit (cost=100.00..1,048,540.13 rows=1 width=4) (actual time=0.796..0.796 rows=0 loops=58)

  • Output: ((opendoorrecord_1.opentime)::date)
24. 34.684 34.684 ↓ 0.0 0 58

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord opendoorrecord_1 (cost=100.00..1,048,540.13 rows=1 width=4) (actual time=0.598..0.598 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)))
25. 0.058 47.966 ↓ 0.0 0 58

Limit (cost=704,222.99..704,223.00 rows=1 width=4) (actual time=0.827..0.827 rows=0 loops=58)

  • Output: ((opendoorrecord_2.opentime)::date)
26. 11.774 47.908 ↓ 0.0 0 58

Sort (cost=704,222.99..704,223.00 rows=1 width=4) (actual time=0.826..0.826 rows=0 loops=58)

  • Output: ((opendoorrecord_2.opentime)::date)
  • Sort Key: ((opendoorrecord_2.opentime)::date)
  • Sort Method: quicksort Memory: 25kB
27. 36.134 36.134 ↓ 14.0 14 58

Foreign Scan on fdw_ailock_business_alpub.opendoorrecord opendoorrecord_2 (cost=100.00..704,222.98 rows=1 width=4) (actual time=0.613..0.623 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)))
28. 16.880 62.000 ↑ 1.0 1 80

Limit (cost=100.00..169.75 rows=1 width=4) (actual time=0.775..0.775 rows=1 loops=80)

  • Output: NULL::integer
29. 45.120 45.120 ↑ 1.0 1 80

Foreign Scan on fdw_ailock_business_alpub.appuseraccount (cost=100.00..169.75 rows=1 width=4) (actual time=0.564..0.564 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.922 ms
Execution time : 495.829 ms