explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Po84

Settings
# exclusive inclusive rows x rows loops node
1. 70.992 1,979.546 ↑ 2.0 33 1

GroupAggregate (cost=2,109,923.86..2,228,962.26 rows=67 width=40) (actual time=1,900.984..1,979.546 rows=33 loops=1)

  • Group Key: _house_nowtenant.bz
  • Filter: (count(DISTINCT _house_nowtenant.lockid) >= 5)
  • Rows Removed by Filter: 50383
2. 59.789 1,908.554 ↑ 216.3 55,022 1

Sort (cost=2,109,923.86..2,139,682.83 rows=11,903,590 width=51) (actual time=1,900.285..1,908.554 rows=55,022 loops=1)

  • Sort Key: _house_nowtenant.bz
  • Sort Method: quicksort Memory: 5835kB
3. 8.353 1,848.765 ↑ 216.3 55,022 1

Subquery Scan on _house_nowtenant (cost=28,084.18..687,711.59 rows=11,903,590 width=51) (actual time=1,057.091..1,848.765 rows=55,022 loops=1)

4. 39.409 1,840.412 ↑ 216.3 55,022 1

Hash Join (cost=28,084.18..568,675.69 rows=11,903,590 width=2,145) (actual time=1,057.090..1,840.412 rows=55,022 loops=1)

  • Hash Cond: (("*SELECT* 1".lockid)::text = (li.lockid)::text)
5.          

CTE all_key

6. 183.440 183.440 ↑ 1.1 62,705 1

Seq Scan on lockkeyinfo lk (cost=0.00..7,360.14 rows=67,907 width=226) (actual time=0.780..183.440 rows=62,705 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255) AND (keystate = 0) AND ((authtime)::text <= (timezone('PRC'::text, now()))::text) AND ((expireddate)::text >= (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 58702
7. 6.853 1,634.750 ↑ 196.6 60,956 1

Append (cost=16,442.34..393,057.60 rows=11,984,091 width=130) (actual time=890.783..1,634.750 rows=60,956 loops=1)

8. 7.760 968.222 ↑ 205.9 57,911 1

Subquery Scan on *SELECT* 1 (cost=16,442.34..314,729.67 rows=11,924,469 width=130) (actual time=890.781..968.222 rows=57,911 loops=1)

9. 39.452 960.462 ↑ 205.9 57,911 1

Merge Join (cost=16,442.34..195,484.98 rows=11,924,469 width=442) (actual time=890.779..960.462 rows=57,911 loops=1)

  • Merge Cond: ((_user_info.account)::text = (all_key.useraccount)::text)
10. 91.560 623.729 ↓ 2.8 99,568 1

Sort (cost=9,634.23..9,722.03 rows=35,120 width=44) (actual time=605.387..623.729 rows=99,568 loops=1)

  • Sort Key: _user_info.account
  • Sort Method: quicksort Memory: 11013kB
11. 15.263 532.169 ↓ 2.9 101,637 1

Subquery Scan on _user_info (cost=5,577.87..6,982.67 rows=35,120 width=44) (actual time=472.273..532.169 rows=101,637 loops=1)

12. 138.867 516.906 ↓ 2.9 101,637 1

HashAggregate (cost=5,577.87..6,631.47 rows=35,120 width=274) (actual time=472.272..516.906 rows=101,637 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk.certid, ac.userid, ac.account)) END, ac.account, CASE WHEN (substr(((COALESCE(bk.certid, ac.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk.certid, ac.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk.certid, ac.userid)) END, max((COALESCE(ac.username, bk.username))::text), max((COALESCE(ac.headpicture, bk.certfrontpic))::text), bk.lockid, bk.bluekeyaccount, max((ac.authstate)::text)
13. 201.082 378.039 ↓ 2.9 101,637 1

HashAggregate (cost=3,821.87..4,875.47 rows=35,120 width=274) (actual time=251.386..378.039 rows=101,637 loops=1)

  • Group Key: COALESCE(bk.certid, ac.userid, ac.account), bk.bluekeyaccount, ac.account, COALESCE(bk.certid, ac.userid)
14. 92.170 176.957 ↓ 2.9 101,637 1

Hash Full Join (cost=3,049.58..3,207.27 rows=35,120 width=356) (actual time=84.191..176.957 rows=101,637 loops=1)

  • Hash Cond: ((bk.certid)::text = (ac.userid)::text)
  • Filter: (length((CASE WHEN (substr((COALESCE(bk.certid, ac.userid, ac.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, ac.userid, ac.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, ac.userid, ac.account) END)::text) > 11)
  • Rows Removed by Filter: 5422
15. 0.702 0.702 ↓ 1.0 3,588 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..49.25 rows=3,565 width=168) (actual time=0.009..0.702 rows=3,588 loops=1)

16. 38.686 84.085 ↑ 1.0 104,503 1

Hash (cost=1,732.59..1,732.59 rows=105,359 width=124) (actual time=84.084..84.085 rows=104,503 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16575kB
17. 45.399 45.399 ↑ 1.0 104,503 1

Seq Scan on appuseraccount ac (cost=0.00..1,732.59 rows=105,359 width=124) (actual time=0.012..45.399 rows=104,503 loops=1)

18. 60.103 297.281 ↑ 1.1 59,926 1

Sort (cost=6,808.11..6,977.88 rows=67,907 width=156) (actual time=285.379..297.281 rows=59,926 loops=1)

  • Sort Key: all_key.useraccount
  • Sort Method: quicksort Memory: 6435kB
19. 237.178 237.178 ↑ 1.1 62,705 1

CTE Scan on all_key (cost=0.00..1,358.14 rows=67,907 width=156) (actual time=0.785..237.178 rows=62,705 loops=1)

20. 0.424 659.675 ↑ 19.6 3,045 1

Subquery Scan on *SELECT* 2 (cost=16,442.34..18,407.48 rows=59,622 width=130) (actual time=637.803..659.675 rows=3,045 loops=1)

21. 11.137 659.251 ↑ 19.6 3,045 1

Merge Join (cost=16,442.34..17,811.26 rows=59,622 width=442) (actual time=637.802..659.251 rows=3,045 loops=1)

  • Merge Cond: (((_user_info_1.lockid)::text = (all_key_1.lockid)::text) AND ((_user_info_1.bluekeyaccount)::text = (all_key_1.useraccount)::text))
22. 25.302 569.337 ↑ 9.2 3,825 1

Sort (cost=9,634.23..9,722.03 rows=35,120 width=70) (actual time=568.576..569.337 rows=3,825 loops=1)

  • Sort Key: _user_info_1.lockid, _user_info_1.bluekeyaccount
  • Sort Method: quicksort Memory: 11252kB
23. 14.606 544.035 ↓ 2.9 101,637 1

Subquery Scan on _user_info_1 (cost=5,577.87..6,982.67 rows=35,120 width=70) (actual time=486.263..544.035 rows=101,637 loops=1)

24. 132.410 529.429 ↓ 2.9 101,637 1

HashAggregate (cost=5,577.87..6,631.47 rows=35,120 width=274) (actual time=486.261..529.429 rows=101,637 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_1.certid, ac_1.userid, ac_1.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_1.certid, ac_1.userid, ac_1.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_1.certid, ac_1.userid, ac_1.account)) END, ac_1.account, CASE WHEN (substr(((COALESCE(bk_1.certid, ac_1.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_1.certid, ac_1.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_1.certid, ac_1.userid)) END, max((COALESCE(ac_1.username, bk_1.username))::text), max((COALESCE(ac_1.headpicture, bk_1.certfrontpic))::text), bk_1.lockid, bk_1.bluekeyaccount, max((ac_1.authstate)::text)
25. 208.346 397.019 ↓ 2.9 101,637 1

HashAggregate (cost=3,821.87..4,875.47 rows=35,120 width=274) (actual time=275.911..397.019 rows=101,637 loops=1)

  • Group Key: COALESCE(bk_1.certid, ac_1.userid, ac_1.account), bk_1.bluekeyaccount, ac_1.account, COALESCE(bk_1.certid, ac_1.userid)
26. 94.323 188.673 ↓ 2.9 101,637 1

Hash Full Join (cost=3,049.58..3,207.27 rows=35,120 width=356) (actual time=94.079..188.673 rows=101,637 loops=1)

  • Hash Cond: ((bk_1.certid)::text = (ac_1.userid)::text)
  • Filter: (length((CASE WHEN (substr((COALESCE(bk_1.certid, ac_1.userid, ac_1.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac_1.userid, ac_1.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac_1.userid, ac_1.account) END)::text) > 11)
  • Rows Removed by Filter: 5422
27. 0.668 0.668 ↓ 1.0 3,588 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..49.25 rows=3,565 width=168) (actual time=0.007..0.668 rows=3,588 loops=1)

28. 45.819 93.682 ↑ 1.0 104,503 1

Hash (cost=1,732.59..1,732.59 rows=105,359 width=124) (actual time=93.682..93.682 rows=104,503 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16575kB
29. 47.863 47.863 ↑ 1.0 104,503 1

Seq Scan on appuseraccount ac_1 (cost=0.00..1,732.59 rows=105,359 width=124) (actual time=0.015..47.863 rows=104,503 loops=1)

30. 67.865 78.777 ↑ 1.1 62,859 1

Sort (cost=6,808.11..6,977.88 rows=67,907 width=156) (actual time=69.197..78.777 rows=62,859 loops=1)

  • Sort Key: all_key_1.lockid, all_key_1.useraccount
  • Sort Method: quicksort Memory: 6435kB
31. 10.912 10.912 ↑ 1.1 62,705 1

CTE Scan on all_key all_key_1 (cost=0.00..1,358.14 rows=67,907 width=156) (actual time=0.005..10.912 rows=62,705 loops=1)

32. 13.900 166.253 ↑ 1.1 43,522 1

Hash (cost=3,690.23..3,690.23 rows=47,318 width=19) (actual time=166.253..166.253 rows=43,522 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2722kB
33. 26.564 152.353 ↑ 1.1 43,522 1

Hash Left Join (cost=2,127.41..3,690.23 rows=47,318 width=19) (actual time=64.292..152.353 rows=43,522 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: 2031
34. 18.490 125.163 ↑ 1.0 45,553 1

Hash Left Join (cost=2,098.17..3,535.31 rows=47,555 width=47) (actual time=63.657..125.163 rows=45,553 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
35. 33.751 105.063 ↑ 1.0 45,553 1

Hash Join (cost=2,025.16..2,811.70 rows=47,555 width=31) (actual time=62.036..105.063 rows=45,553 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
36. 9.388 9.388 ↑ 1.0 47,590 1

Seq Scan on lockinfo li (cost=0.00..661.48 rows=47,638 width=51) (actual time=0.011..9.388 rows=47,590 loops=1)

37. 24.021 61.924 ↓ 1.0 70,443 1

Hash (cost=1,150.68..1,150.68 rows=69,958 width=45) (actual time=61.924..61.924 rows=70,443 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6451kB
38. 37.903 37.903 ↓ 1.0 70,443 1

Seq Scan on houseinfo hi (cost=0.00..1,150.68 rows=69,958 width=45) (actual time=0.007..37.903 rows=70,443 loops=1)

39. 0.330 1.610 ↓ 1.0 1,563 1

Hash (cost=53.53..53.53 rows=1,559 width=28) (actual time=1.610..1.610 rows=1,563 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
40. 0.508 1.280 ↓ 1.0 1,563 1

Hash Right Join (cost=37.28..53.53 rows=1,559 width=28) (actual time=0.629..1.280 rows=1,563 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
41. 0.157 0.157 ↓ 1.1 1,280 1

Seq Scan on "行政区划与管理单位代码映射" ys (cost=0.00..13.11 rows=1,191 width=32) (actual time=0.005..0.157 rows=1,280 loops=1)

42. 0.300 0.615 ↑ 1.0 1,546 1

Hash (cost=17.79..17.79 rows=1,559 width=28) (actual time=0.615..0.615 rows=1,546 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
43. 0.315 0.315 ↑ 1.0 1,546 1

Seq Scan on "行政区划代码" xz (cost=0.00..17.79 rows=1,559 width=28) (actual time=0.009..0.315 rows=1,546 loops=1)

44. 0.370 0.626 ↑ 1.0 1,202 1

Hash (cost=14.22..14.22 rows=1,202 width=44) (actual time=0.626..0.626 rows=1,202 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
45. 0.256 0.256 ↑ 1.0 1,202 1

Seq Scan on "管理单位代码" gl (cost=0.00..14.22 rows=1,202 width=44) (actual time=0.006..0.256 rows=1,202 loops=1)