explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pPxy

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 48,141.470 ↓ 29.4 500 1

Limit (cost=22,719.84..22,720.52 rows=17 width=535) (actual time=48,140.770..48,141.470 rows=500 loops=1)

2. 0.156 48,141.377 ↓ 29.4 500 1

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

3. 0.273 48,141.221 ↓ 29.4 500 1

Unique (cost=22,719.84..22,720.35 rows=17 width=633) (actual time=48,140.766..48,141.221 rows=500 loops=1)

4. 38,183.697 48,140.948 ↓ 33.5 569 1

Sort (cost=22,719.84..22,719.89 rows=17 width=633) (actual time=48,140.764..48,140.948 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: 68000kB
5. 432.550 9,957.251 ↓ 13,420.9 228,156 1

WindowAgg (cost=22,718.90..22,719.50 rows=17 width=633) (actual time=9,432.398..9,957.251 rows=228,156 loops=1)

6. 784.028 9,524.701 ↓ 13,420.9 228,156 1

Sort (cost=22,718.90..22,718.94 rows=17 width=677) (actual time=9,432.372..9,524.701 rows=228,156 loops=1)

  • Sort Key: user_info.account, lk.lockid
  • Sort Method: external merge Disk: 71448kB
7. 353.901 8,740.673 ↓ 13,420.9 228,156 1

Nested Loop Left Join (cost=14,063.36..22,718.55 rows=17 width=677) (actual time=2.044..8,740.673 rows=228,156 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: 21711
8. 162.999 7,887.038 ↓ 14,698.1 249,867 1

Hash Left Join (cost=14,063.08..22,713.16 rows=17 width=665) (actual time=2.022..7,887.038 rows=249,867 loops=1)

  • Hash Cond: (xz.id = ys."行政区划_id")
9. 198.348 7,723.464 ↓ 14,698.1 249,867 1

Hash Left Join (cost=14,031.81..22,681.64 rows=17 width=665) (actual time=1.412..7,723.464 rows=249,867 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 389.889 7,524.067 ↓ 14,698.1 249,867 1

Nested Loop (cost=13,992.84..22,642.43 rows=17 width=649) (actual time=0.330..7,524.067 rows=249,867 loops=1)

11. 335.880 5,805.293 ↓ 15,633.9 265,777 1

Nested Loop (cost=13,992.42..22,634.86 rows=17 width=590) (actual time=0.300..5,805.293 rows=265,777 loops=1)

12. 428.250 4,138.258 ↓ 15,660.6 266,231 1

Nested Loop Left Join (cost=13,992.00..22,623.90 rows=17 width=1,002) (actual time=0.252..4,138.258 rows=266,231 loops=1)

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

CTE all_key

14. 53.434 696.796 ↓ 1.0 345,731 1

Append (cost=0.00..13,991.30 rows=344,527 width=231) (actual time=0.145..696.796 rows=345,731 loops=1)

15. 508.921 508.921 ↓ 1.0 156,051 1

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

16. 134.441 134.441 ↑ 1.0 189,680 1

Seq Scan on keyhistoryinfo khi (cost=0.00..2,712.15 rows=189,845 width=226) (actual time=0.019..134.441 rows=189,680 loops=1)

17. 301.133 3,157.844 ↓ 16,240.1 276,082 1

Nested Loop Left Join (cost=0.42..8,624.01 rows=17 width=332) (actual time=0.217..3,157.844 rows=276,082 loops=1)

18. 1,200.219 1,200.219 ↓ 16,240.1 276,082 1

CTE Scan on all_key lk (cost=0.00..8,613.18 rows=17 width=188) (actual time=0.152..1,200.219 rows=276,082 loops=1)

  • Filter: ((valid_state = ANY ('{0,1}'::text[])) AND (keytype = 0))
  • Rows Removed by Filter: 69649
19. 1,656.492 1,656.492 ↑ 1.0 1 276,082

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

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

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,082)

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

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

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

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

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

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

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

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

25. 0.302 0.575 ↑ 1.0 1,332 1

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

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

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

27. 499.734 499.734 ↑ 1.0 1 249,867

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,867)

  • Index Cond: (id = ys."管理单位代码_id")
Planning time : 2.380 ms
Execution time : 48,163.425 ms