explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LzC

Settings
# exclusive inclusive rows x rows loops node
1. 72.454 2,274.891 ↑ 1.1 80,108 1

Hash Left Join (cost=35,150.76..40,204.76 rows=85,467 width=368) (actual time=2,030.145..2,274.891 rows=80,108 loops=1)

  • Hash Cond: ((li.lockid)::text = (lock_key_count.lockid)::text)
2.          

CTE lock_key_count

3. 97.043 1,886.200 ↓ 1,712.0 58,209 1

GroupAggregate (cost=31,707.51..31,708.10 rows=34 width=106) (actual time=1,771.781..1,886.200 rows=58,209 loops=1)

  • Group Key: _user_key.lockid
4. 117.821 1,789.157 ↓ 3,097.4 105,311 1

Sort (cost=31,707.51..31,707.59 rows=34 width=196) (actual time=1,771.757..1,789.157 rows=105,311 loops=1)

  • Sort Key: _user_key.lockid
  • Sort Method: quicksort Memory: 17882kB
5. 15.459 1,671.336 ↓ 3,097.4 105,311 1

Subquery Scan on _user_key (cost=18,464.46..31,706.65 rows=34 width=196) (actual time=0.079..1,671.336 rows=105,311 loops=1)

6. 22.771 1,655.877 ↓ 3,097.4 105,311 1

Nested Loop Left Join (cost=18,464.46..31,706.31 rows=34 width=1,084) (actual time=0.078..1,655.877 rows=105,311 loops=1)

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

CTE all_key

8. 45.083 548.960 ↑ 1.0 457,994 1

Append (cost=0.00..18,463.76 rows=459,306 width=231) (actual time=0.038..548.960 rows=457,994 loops=1)

9. 403.333 403.333 ↑ 1.0 201,301 1

Seq Scan on lockkeyinfo lk (cost=0.00..12,491.30 rows=202,433 width=238) (actual time=0.037..403.333 rows=201,301 loops=1)

10. 100.544 100.544 ↑ 1.0 256,693 1

Seq Scan on keyhistoryinfo khi (cost=0.00..3,675.93 rows=256,873 width=226) (actual time=0.011..100.544 rows=256,693 loops=1)

11. 71.453 1,417.612 ↓ 3,169.0 107,747 1

Nested Loop Left Join (cost=0.42..13,226.72 rows=34 width=284) (actual time=0.066..1,417.612 rows=107,747 loops=1)

12. 915.171 915.171 ↓ 3,169.0 107,747 1

CTE Scan on all_key lk_1 (cost=0.00..13,205.05 rows=34 width=254) (actual time=0.045..915.171 rows=107,747 loops=1)

  • Filter: ((authtime IS NOT NULL) AND (userrole <> 255) AND (valid_state = '0'::text) AND (keytype = ANY ('{0,1,2}'::integer[])))
  • Rows Removed by Filter: 350247
13. 430.988 430.988 ↑ 1.0 1 107,747

Index Scan using "pk_appuseracc_1588852933_50 " on appuseraccount user_info (cost=0.42..0.64 rows=1 width=30) (actual time=0.004..0.004 rows=1 loops=107,747)

  • Index Cond: ((account)::text = (lk_1.useraccount)::text)
14. 215.494 215.494 ↓ 0.0 0 107,747

Index Scan using "pk_bluekeyuse_1588853067_60 " on bluekeyuserinfo bk (cost=0.28..0.46 rows=1 width=56) (actual time=0.002..0.002 rows=0 loops=107,747)

  • Index Cond: ((bluekeyaccount)::text = (lk_1.useraccount)::text)
  • Filter: ((lockid)::text = (lk_1.lockid)::text)
  • Rows Removed by Filter: 0
15. 48.336 281.144 ↑ 1.1 80,108 1

Hash Left Join (cost=3,441.55..6,251.70 rows=85,467 width=338) (actual time=108.831..281.144 rows=80,108 loops=1)

  • Hash Cond: (ys."管理单位代码_id" = gl.id)
  • 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: 2523
16. 34.928 232.151 ↑ 1.0 82,631 1

Hash Left Join (cost=3,411.84..5,995.02 rows=85,896 width=280) (actual time=108.165..232.151 rows=82,631 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
17. 75.270 195.828 ↑ 1.0 82,631 1

Hash Join (cost=3,334.36..4,740.37 rows=85,896 width=232) (actual time=106.760..195.828 rows=82,631 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
18. 13.877 13.877 ↑ 1.0 85,680 1

Seq Scan on lockinfo li (cost=0.00..1,180.28 rows=85,988 width=72) (actual time=0.006..13.877 rows=85,680 loops=1)

19. 49.326 106.681 ↑ 1.0 117,447 1

Hash (cost=1,866.27..1,866.27 rows=117,447 width=228) (actual time=106.681..106.681 rows=117,447 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 29989kB
20. 57.355 57.355 ↑ 1.0 117,447 1

Seq Scan on houseinfo hi (cost=0.00..1,866.27 rows=117,447 width=228) (actual time=0.003..57.355 rows=117,447 loops=1)

21. 0.325 1.395 ↓ 1.0 1,639 1

Hash (cost=57.10..57.10 rows=1,630 width=60) (actual time=1.395..1.395 rows=1,639 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 164kB
22. 0.436 1.070 ↓ 1.0 1,639 1

Hash Right Join (cost=38.98..57.10 rows=1,630 width=60) (actual time=0.531..1.070 rows=1,639 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
23. 0.112 0.112 ↑ 1.0 1,332 1

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

24. 0.282 0.522 ↓ 1.0 1,632 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 168kB
25. 0.240 0.240 ↓ 1.0 1,632 1

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

26. 0.373 0.657 ↓ 1.0 1,224 1

Hash (cost=14.43..14.43 rows=1,223 width=90) (actual time=0.657..0.657 rows=1,224 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 163kB
27. 0.284 0.284 ↓ 1.0 1,224 1

Seq Scan on "管理单位代码" gl (cost=0.00..14.43 rows=1,223 width=90) (actual time=0.005..0.284 rows=1,224 loops=1)

28. 12.484 1,921.293 ↓ 1,712.0 58,209 1

Hash (cost=0.68..0.68 rows=34 width=106) (actual time=1,921.293..1,921.293 rows=58,209 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3903kB
29. 1,908.809 1,908.809 ↓ 1,712.0 58,209 1

CTE Scan on lock_key_count (cost=0.00..0.68 rows=34 width=106) (actual time=1,771.785..1,908.809 rows=58,209 loops=1)