explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J2yx

Settings
# exclusive inclusive rows x rows loops node
1. 0.068 14,426.024 ↓ 29.4 500 1

Limit (cost=22,719.84..22,720.52 rows=17 width=535) (actual time=14,425.379..14,426.024 rows=500 loops=1)

2. 0.166 14,425.956 ↓ 29.4 500 1

Subquery Scan on app钥匙使用人信息 (cost=22,719.84..22,720.52 rows=17 width=535) (actual time=14,425.378..14,425.956 rows=500 loops=1)

3. 0.262 14,425.790 ↓ 29.4 500 1

Unique (cost=22,719.84..22,720.35 rows=17 width=633) (actual time=14,425.374..14,425.790 rows=500 loops=1)

4. 904.651 14,425.528 ↓ 33.5 569 1

Sort (cost=22,719.84..22,719.89 rows=17 width=633) (actual time=14,425.372..14,425.528 rows=569 loops=1)

  • Sort Key: user_info.account, (CASE WHEN (substr((COALESCE(bk.certid, user_info.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, user_info.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, user_info.userid) END), (COALESCE(user_info.username, bk.username)), (COALESCE(user_info.headpicture, bk.certfrontpic, bk.certbackpic)), user_info.authstate, (COALESCE(user_info.createtime, bk.createtime)), ((((hi.detailaddr)::text || '#'::text) || (hi.housenumber)::text)), hi.xzqhdm, (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl."代码" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl."上级单位代码" ELSE NULL::text END), li.lockid, (min(lk.valid_state) OVER (?))
  • Sort Method: external merge Disk: 68016kB
5. 580.850 13,520.877 ↓ 13,423.6 228,201 1

WindowAgg (cost=22,718.90..22,719.50 rows=17 width=633) (actual time=12,786.536..13,520.877 rows=228,201 loops=1)

6. 1,182.766 12,940.027 ↓ 13,423.6 228,201 1

Sort (cost=22,718.90..22,718.94 rows=17 width=677) (actual time=12,786.427..12,940.027 rows=228,201 loops=1)

  • Sort Key: user_info.account, lk.lockid
  • Sort Method: external merge Disk: 71464kB
7. 447.548 11,757.261 ↓ 13,423.6 228,201 1

Nested Loop Left Join (cost=14,063.36..22,718.55 rows=17 width=677) (actual time=7.858..11,757.261 rows=228,201 loops=1)

  • Filter: (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl."代码" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl."上级单位代码" ELSE NULL::text END IS NOT NULL)
  • Rows Removed by Filter: 21712
8. 179.522 10,809.887 ↓ 14,700.8 249,913 1

Hash Left Join (cost=14,063.08..22,713.16 rows=17 width=665) (actual time=7.672..10,809.887 rows=249,913 loops=1)

  • Hash Cond: (xz.id = ys."行政区划_id")
9. 226.257 10,629.394 ↓ 14,700.8 249,913 1

Hash Left Join (cost=14,031.81..22,681.64 rows=17 width=665) (actual time=6.692..10,629.394 rows=249,913 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 426.359 10,402.340 ↓ 14,700.8 249,913 1

Nested Loop (cost=13,992.84..22,642.43 rows=17 width=649) (actual time=5.884..10,402.340 rows=249,913 loops=1)

11. 437.106 8,115.220 ↓ 15,636.6 265,823 1

Nested Loop (cost=13,992.42..22,634.86 rows=17 width=590) (actual time=3.235..8,115.220 rows=265,823 loops=1)

12. 496.728 5,814.175 ↓ 15,663.4 266,277 1

Nested Loop Left Join (cost=13,992.00..22,623.90 rows=17 width=1,002) (actual time=0.253..5,814.175 rows=266,277 loops=1)

  • Filter: (COALESCE(bk.certid, user_info.userid, user_info.account) IS NOT NULL)
  • Rows Removed by Filter: 9844
13.          

CTE all_key

14. 60.555 951.071 ↓ 1.0 345,775 1

Append (cost=0.00..13,991.30 rows=344,527 width=231) (actual time=0.030..951.071 rows=345,775 loops=1)

15. 565.309 565.309 ↓ 1.0 156,078 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..9,556.51 rows=154,682 width=238) (actual time=0.029..565.309 rows=156,078 loops=1)

16. 325.207 325.207 ↑ 1.0 189,697 1

Seq Scan on keyhistoryinfo khi (cost=0.00..2,712.15 rows=189,845 width=226) (actual time=1.220..325.207 rows=189,697 loops=1)

17. 509.249 4,765.205 ↓ 16,242.4 276,121 1

Nested Loop Left Join (cost=0.42..8,624.01 rows=17 width=332) (actual time=0.238..4,765.205 rows=276,121 loops=1)

18. 1,494.746 1,494.746 ↓ 16,242.4 276,121 1

CTE Scan on all_key lk (cost=0.00..8,613.18 rows=17 width=188) (actual time=0.035..1,494.746 rows=276,121 loops=1)

  • Filter: ((valid_state = ANY ('{0,1}'::text[])) AND (keytype = 0))
  • Rows Removed by Filter: 69654
19. 2,761.210 2,761.210 ↑ 1.0 1 276,121

Index Scan using "pk_appuseracc_1578488483_2 " on appuseraccount user_info (cost=0.42..0.64 rows=1 width=144) (actual time=0.010..0.010 rows=1 loops=276,121)

  • Index Cond: ((account)::text = (lk.useraccount)::text)
20. 552.242 552.242 ↓ 0.0 0 276,121

Index Scan using "pk_bluekeyuse_1570385661_103 " on bluekeyuserinfo bk (cost=0.28..0.48 rows=1 width=325) (actual time=0.002..0.002 rows=0 loops=276,121)

  • Index Cond: ((bluekeyaccount)::text = (lk.useraccount)::text)
  • Filter: ((lockid)::text = (lk.lockid)::text)
21. 1,863.939 1,863.939 ↑ 1.0 1 266,277

Index Scan using "pk_lockinfo_1578488557_5 " on lockinfo li (cost=0.42..0.64 rows=1 width=52) (actual time=0.007..0.007 rows=1 loops=266,277)

  • Index Cond: ((lockid)::text = (lk.lockid)::text)
22. 1,860.761 1,860.761 ↑ 1.0 1 265,823

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo hi (cost=0.42..0.45 rows=1 width=126) (actual time=0.007..0.007 rows=1 loops=265,823)

  • Index Cond: ((houseid)::text = (li.houseid)::text)
23. 0.404 0.797 ↓ 1.0 1,632 1

Hash (cost=18.60..18.60 rows=1,630 width=28) (actual time=0.797..0.797 rows=1,632 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
24. 0.393 0.393 ↓ 1.0 1,632 1

Seq Scan on "行政区划代码" xz (cost=0.00..18.60 rows=1,630 width=28) (actual time=0.008..0.393 rows=1,632 loops=1)

25. 0.380 0.971 ↑ 1.0 1,332 1

Hash (cost=14.62..14.62 rows=1,332 width=32) (actual time=0.971..0.971 rows=1,332 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 100kB
26. 0.591 0.591 ↑ 1.0 1,332 1

Seq Scan on "行政区划与管理单位代码映射" ys (cost=0.00..14.62 rows=1,332 width=32) (actual time=0.134..0.591 rows=1,332 loops=1)

27. 499.826 499.826 ↑ 1.0 1 249,913

Index Scan using "管理单位代码_pkey" on "管理单位代码" gl (cost=0.28..0.30 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=249,913)

  • Index Cond: (id = ys."管理单位代码_id")
Planning time : 2.412 ms
Execution time : 14,452.830 ms