explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dyjx

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 6,501.665 ↑ 234,539.4 23 1

GroupAggregate (cost=19,081,830.60..21,921,227.71 rows=5,394,406 width=426) (actual time=6,501.629..6,501.665 rows=23 loops=1)

  • Group Key: new_people.bz, new_people.come_time, hi.detailaddr, hi.housenumber, ""*SELECT* 1"".authtime
2.          

CTE xzqh_id

3. 0.000 0.329 ↓ 0.0 0 1

Nested Loop (cost=17.79..36.24 rows=2 width=28) (actual time=0.329..0.329 rows=0 loops=1)

4. 0.007 0.329 ↓ 0.0 0 1

Hash Join (cost=17.51..35.64 rows=2 width=16) (actual time=0.329..0.329 rows=0 loops=1)

  • Hash Cond: (ys_1."管理单位代码_id" = gl.id)
5. 0.003 0.003 ↑ 1,332.0 1 1

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

6. 0.001 0.319 ↓ 0.0 0 1

Hash (cost=17.49..17.49 rows=2 width=16) (actual time=0.318..0.319 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
7. 0.318 0.318 ↓ 0.0 0 1

Seq Scan on "管理单位代码" gl (cost=0.00..17.49 rows=2 width=16) (actual time=0.318..0.318 rows=0 loops=1)

  • Filter: (("代码" = ANY ('{330522}'::text[])) OR ("上级单位代码" = ANY ('{330522}'::text[])))
  • Rows Removed by Filter: 1,223
8. 0.000 0.000 ↓ 0.0 0

Index Scan using "行政区划代码_pkey" on "行政区划代码" xz_1 (cost=0.28..0.30 rows=1 width=44) (never executed)

  • Index Cond: (id = ys_1."行政区划_id")
9.          

CTE new_people

10. 24.484 2,958.672 ↓ 23.0 23 1

GroupAggregate (cost=58,813.75..58,844.01 rows=1 width=64) (actual time=2,926.754..2,958.672 rows=23 loops=1)

  • Group Key: _house_user.bz
  • Filter: (min((_house_user.authtime)::text) ~~ '2020-02-10%'::text)
  • Rows Removed by Filter: 38,683
11.          

Initplan (for GroupAggregate)

12. 0.006 0.014 ↑ 1.0 1 1

Aggregate (cost=1.50..1.51 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)

13. 0.008 0.008 ↑ 100.0 1 1

Function Scan on unnest t (cost=0.00..1.00 rows=100 width=32) (actual time=0.007..0.008 rows=1 loops=1)

14. 65.821 2,934.174 ↓ 20.8 57,710 1

Sort (cost=58,812.23..58,819.17 rows=2,776 width=90) (actual time=2,926.017..2,934.174 rows=57,710 loops=1)

  • Sort Key: _house_user.bz
  • Sort Method: quicksort Memory: 6,045kB
15. 35.763 2,868.353 ↓ 20.8 57,710 1

Subquery Scan on _house_user (cost=29,314.27..58,653.46 rows=2,776 width=90) (actual time=1,201.660..2,868.353 rows=57,710 loops=1)

  • Filter: ((hashed SubPlan 2) OR ((_house_user.xzqhdm)::text ~~ ANY ($3)))
  • Rows Removed by Filter: 37,130
16. 73.236 2,832.259 ↓ 17.9 94,840 1

Hash Left Join (cost=29,314.22..58,521.09 rows=5,293 width=2,210) (actual time=1,201.296..2,832.259 rows=94,840 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_2."代码")
17.          

CTE all_key

18. 20.990 307.382 ↑ 1.0 167,928 1

Append (cost=0.00..11,888.20 rows=173,654 width=283) (actual time=0.030..307.382 rows=167,928 loops=1)

19. 174.783 174.783 ↑ 1.0 74,482 1

Seq Scan on lockkeyinfo lk (cost=0.00..5,315.18 rows=76,180 width=290) (actual time=0.030..174.783 rows=74,482 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 51,900
20. 111.609 111.609 ↑ 1.0 93,446 1

Seq Scan on keyhistoryinfo khi (cost=0.00..3,968.20 rows=97,474 width=278) (actual time=0.016..111.609 rows=93,446 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 61,559
21. 87.399 2,757.279 ↓ 17.9 94,840 1

Nested Loop (cost=17,348.54..46,482.95 rows=5,293 width=102) (actual time=1,199.538..2,757.279 rows=94,840 loops=1)

22. 68.948 2,253.312 ↓ 19.6 104,142 1

Hash Join (cost=17,348.12..44,121.76 rows=5,301 width=122) (actual time=1,199.505..2,253.312 rows=104,142 loops=1)

  • Hash Cond: (("*SELECT* 1_1".lockid)::text = (li_1.lockid)::text)
23. 14.589 2,153.650 ↓ 19.7 104,255 1

Append (cost=15,929.84..42,689.56 rows=5,301 width=188) (actual time=1,168.745..2,153.650 rows=104,255 loops=1)

24. 16.191 1,348.797 ↓ 18.0 95,009 1

Subquery Scan on *SELECT* 1_1 (cost=15,929.84..21,164.20 rows=5,275 width=188) (actual time=1,168.744..1,348.797 rows=95,009 loops=1)

25. 97.374 1,332.606 ↓ 18.0 95,009 1

Hash Join (cost=15,929.84..21,111.45 rows=5,275 width=538) (actual time=1,168.742..1,332.606 rows=95,009 loops=1)

  • Hash Cond: ((ac_3.account)::text = (all_key_2.useraccount)::text)
26. 183.700 700.683 ↓ 1.0 121,184 1

HashAggregate (cost=11,588.38..15,105.31 rows=117,231 width=274) (actual time=634.177..700.683 rows=121,184 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_3.certid, ac_3.userid, ac_3.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_3.certid, ac_3.userid, ac_3.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_3.certid, ac_3.userid, ac_3.account)) END, ac_3.account, CASE WHEN (substr(((COALESCE(bk_3.certid, ac_3.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_3.certid, ac_3.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_3.certid, ac_3.userid)) END, max((COALESCE(ac_3.username, bk_3.username))::text), max((COALESCE(ac_3.headpicture, bk_3.certfrontpic))::text), bk_3.lockid, bk_3.bluekeyaccount, max((ac_3.authstate)::text)
27. 268.985 516.983 ↓ 1.0 121,184 1

HashAggregate (cost=5,726.83..9,243.76 rows=117,231 width=274) (actual time=363.061..516.983 rows=121,184 loops=1)

  • Group Key: COALESCE(bk_3.certid, ac_3.userid, ac_3.account), bk_3.bluekeyaccount, ac_3.account, COALESCE(bk_3.certid, ac_3.userid)
28. 142.932 247.998 ↓ 1.0 121,184 1

Hash Full Join (cost=3,482.87..3,675.29 rows=117,231 width=356) (actual time=105.754..247.998 rows=121,184 loops=1)

  • Hash Cond: ((bk_3.certid)::text = (ac_3.userid)::text)
  • Filter: ((CASE WHEN (substr((COALESCE(bk_3.certid, ac_3.userid, ac_3.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_3.certid, ac_3.userid, ac_3.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_3.certid, ac_3.userid, ac_3.account) END IS NOT NULL) AND (CASE WHEN (substr((COALESCE(bk_3.certid, ac_3.userid, ac_3.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_3.certid, ac_3.userid, ac_3.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_3.certid, ac_3.userid, ac_3.account) END IS NOT NULL))
29. 0.825 0.825 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_3 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.008..0.825 rows=3,876 loops=1)

30. 49.733 104.241 ↑ 1.0 118,400 1

Hash (cost=2,002.72..2,002.72 rows=118,412 width=124) (actual time=104.240..104.241 rows=118,400 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 18,650kB
31. 54.508 54.508 ↑ 1.0 118,400 1

Seq Scan on appuseraccount ac_3 (cost=0.00..2,002.72 rows=118,412 width=124) (actual time=0.024..54.508 rows=118,400 loops=1)

32. 38.776 534.549 ↓ 12,306.8 110,761 1

Hash (cost=4,341.35..4,341.35 rows=9 width=214) (actual time=534.549..534.549 rows=110,761 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,202kB
33. 495.773 495.773 ↓ 12,306.8 110,761 1

CTE Scan on all_key all_key_2 (cost=0.00..4,341.35 rows=9 width=214) (actual time=0.036..495.773 rows=110,761 loops=1)

  • Filter: ((authtime IS NOT NULL) AND (keytype = ANY ('{0,2}'::integer[])) AND (keystate = 0))
  • Rows Removed by Filter: 57,167
34. 1.632 790.264 ↓ 355.6 9,246 1

Subquery Scan on *SELECT* 2_1 (cost=15,929.86..21,498.85 rows=26 width=188) (actual time=714.994..790.264 rows=9,246 loops=1)

35. 17.221 788.632 ↓ 355.6 9,246 1

Hash Join (cost=15,929.86..21,498.60 rows=26 width=538) (actual time=714.993..788.632 rows=9,246 loops=1)

  • Hash Cond: (((bk_4.bluekeyaccount)::text = (all_key_3.useraccount)::text) AND ((bk_4.lockid)::text = (all_key_3.lockid)::text))
36. 174.392 685.867 ↓ 1.0 121,184 1

HashAggregate (cost=11,588.38..15,105.31 rows=117,231 width=274) (actual time=629.402..685.867 rows=121,184 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_4.certid, ac_4.userid, ac_4.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_4.certid, ac_4.userid, ac_4.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_4.certid, ac_4.userid, ac_4.account)) END, ac_4.account, CASE WHEN (substr(((COALESCE(bk_4.certid, ac_4.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_4.certid, ac_4.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_4.certid, ac_4.userid)) END, max((COALESCE(ac_4.username, bk_4.username))::text), max((COALESCE(ac_4.headpicture, bk_4.certfrontpic))::text), bk_4.lockid, bk_4.bluekeyaccount, max((ac_4.authstate)::text)
37. 266.770 511.475 ↓ 1.0 121,184 1

HashAggregate (cost=5,726.83..9,243.76 rows=117,231 width=274) (actual time=355.986..511.475 rows=121,184 loops=1)

  • Group Key: COALESCE(bk_4.certid, ac_4.userid, ac_4.account), bk_4.bluekeyaccount, ac_4.account, COALESCE(bk_4.certid, ac_4.userid)
38. 137.561 244.705 ↓ 1.0 121,184 1

Hash Full Join (cost=3,482.87..3,675.29 rows=117,231 width=356) (actual time=106.823..244.705 rows=121,184 loops=1)

  • Hash Cond: ((bk_4.certid)::text = (ac_4.userid)::text)
  • Filter: ((CASE WHEN (substr((COALESCE(bk_4.certid, ac_4.userid, ac_4.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_4.certid, ac_4.userid, ac_4.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_4.certid, ac_4.userid, ac_4.account) END IS NOT NULL) AND (CASE WHEN (substr((COALESCE(bk_4.certid, ac_4.userid, ac_4.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_4.certid, ac_4.userid, ac_4.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_4.certid, ac_4.userid, ac_4.account) END IS NOT NULL))
39. 0.863 0.863 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_4 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.010..0.863 rows=3,876 loops=1)

40. 50.471 106.281 ↑ 1.0 118,400 1

Hash (cost=2,002.72..2,002.72 rows=118,412 width=124) (actual time=106.281..106.281 rows=118,400 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 18,650kB
41. 55.810 55.810 ↑ 1.0 118,400 1

Seq Scan on appuseraccount ac_4 (cost=0.00..2,002.72 rows=118,412 width=124) (actual time=0.018..55.810 rows=118,400 loops=1)

42. 36.659 85.544 ↓ 12,306.8 110,761 1

Hash (cost=4,341.35..4,341.35 rows=9 width=214) (actual time=85.544..85.544 rows=110,761 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,202kB
43. 48.885 48.885 ↓ 12,306.8 110,761 1

CTE Scan on all_key all_key_3 (cost=0.00..4,341.35 rows=9 width=214) (actual time=0.007..48.885 rows=110,761 loops=1)

  • Filter: ((authtime IS NOT NULL) AND (keytype = ANY ('{0,2}'::integer[])) AND (keystate = 0))
  • Rows Removed by Filter: 57,167
44. 12.759 30.714 ↑ 1.0 52,796 1

Hash (cost=740.96..740.96 rows=54,186 width=51) (actual time=30.714..30.714 rows=52,796 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,871kB
45. 17.955 17.955 ↑ 1.0 52,796 1

Seq Scan on lockinfo li_1 (cost=0.00..740.96 rows=54,186 width=51) (actual time=0.017..17.955 rows=52,796 loops=1)

46. 416.568 416.568 ↑ 1.0 1 104,142

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo hi_1 (cost=0.42..0.45 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=104,142)

  • Index Cond: ((houseid)::text = (li_1.houseid)::text)
47. 0.346 1.744 ↓ 1.0 1,639 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
48. 0.533 1.398 ↓ 1.0 1,639 1

Hash Right Join (cost=38.98..57.10 rows=1,630 width=12) (actual time=0.701..1.398 rows=1,639 loops=1)

  • Hash Cond: (ys_2."行政区划_id" = xz_2.id)
49. 0.177 0.177 ↑ 1.0 1,332 1

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

50. 0.349 0.688 ↓ 1.0 1,632 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
51. 0.339 0.339 ↓ 1.0 1,632 1

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

52.          

SubPlan (for Subquery Scan)

53. 0.331 0.331 ↓ 0.0 0 1

CTE Scan on xzqh_id (cost=0.00..0.04 rows=2 width=32) (actual time=0.330..0.331 rows=0 loops=1)

54. 0.070 6,501.620 ↑ 4,220,383.4 24 1

Sort (cost=19,022,950.36..19,276,173.36 rows=101,289,202 width=368) (actual time=6,501.617..6,501.620 rows=24 loops=1)

  • Sort Key: new_people.bz, new_people.come_time, hi.detailaddr, hi.housenumber
  • Sort Method: quicksort Memory: 37kB
55. 13.536 6,501.550 ↑ 4,220,383.4 24 1

Merge Right Join (cost=2,092,194.64..3,615,834.41 rows=101,289,202 width=368) (actual time=6,430.821..6,501.550 rows=24 loops=1)

  • Merge Cond: (((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 = (new_people.bz)::text)
56. 41.296 634.153 ↑ 1.3 93,789 1

Unique (cost=19,195.63..21,859.90 rows=118,412 width=274) (actual time=573.769..634.153 rows=93,789 loops=1)

57. 212.271 592.857 ↑ 1.3 93,789 1

Sort (cost=19,195.63..19,491.66 rows=118,412 width=274) (actual time=573.761..592.857 rows=93,789 loops=1)

  • Sort 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))
  • Sort Method: quicksort Memory: 34,133kB
58. 235.127 380.586 ↓ 1.0 121,184 1

HashAggregate (cost=5,665.02..9,217.38 rows=118,412 width=274) (actual time=226.036..380.586 rows=121,184 loops=1)

  • Group Key: COALESCE(bk.certid, ac.userid, ac.account), bk.bluekeyaccount, ac.account, COALESCE(bk.certid, ac.userid)
59. 46.652 145.459 ↓ 1.0 121,184 1

Hash Full Join (cost=3,482.87..3,592.81 rows=118,412 width=356) (actual time=98.117..145.459 rows=121,184 loops=1)

  • Hash Cond: ((bk.certid)::text = (ac.userid)::text)
60. 0.843 0.843 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.015..0.843 rows=3,876 loops=1)

61. 41.787 97.964 ↑ 1.0 118,400 1

Hash (cost=2,002.72..2,002.72 rows=118,412 width=124) (actual time=97.964..97.964 rows=118,400 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 18,650kB
62. 56.177 56.177 ↑ 1.0 118,400 1

Seq Scan on appuseraccount ac (cost=0.00..2,002.72 rows=118,412 width=124) (actual time=0.092..56.177 rows=118,400 loops=1)

63. 0.082 5,853.861 ↑ 7,129.0 24 1

Sort (cost=2,072,999.00..2,073,426.75 rows=171,097 width=260) (actual time=5,853.856..5,853.861 rows=24 loops=1)

  • Sort Key: new_people.bz
  • Sort Method: quicksort Memory: 31kB
64. 26.044 5,853.779 ↑ 7,439.0 23 1

Hash Join (cost=51,215.93..2,058,126.86 rows=171,097 width=260) (actual time=4,549.845..5,853.779 rows=23 loops=1)

  • Hash Cond: (("*SELECT* 1".authtime)::text = new_people.come_time)
65. 126.163 2,869.019 ↑ 226.6 150,988 1

Hash Join (cost=51,215.90..1,585,900.14 rows=34,219,325 width=1,782) (actual time=1,566.915..2,869.019 rows=150,988 loops=1)

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

CTE all_key

67. 21.400 308.655 ↑ 1.0 167,928 1

Append (cost=0.00..11,888.20 rows=173,654 width=283) (actual time=0.029..308.655 rows=167,928 loops=1)

68. 176.097 176.097 ↑ 1.0 74,482 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..5,315.18 rows=76,180 width=290) (actual time=0.029..176.097 rows=74,482 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 51,900
69. 111.158 111.158 ↑ 1.0 93,446 1

Seq Scan on keyhistoryinfo khi_1 (cost=0.00..3,968.20 rows=97,474 width=278) (actual time=0.016..111.158 rows=93,446 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 61,559
70. 20.723 2,573.352 ↑ 210.8 162,596 1

Append (cost=34,709.99..1,098,686.40 rows=34,270,554 width=214) (actual time=1,397.351..2,573.352 rows=162,596 loops=1)

71. 24.336 1,584.069 ↑ 222.4 153,343 1

Subquery Scan on *SELECT* 1 (cost=34,709.99..887,500.76 rows=34,100,054 width=214) (actual time=1,397.349..1,584.069 rows=153,343 loops=1)

72. 96.927 1,559.733 ↑ 222.4 153,343 1

Merge Join (cost=34,709.99..546,500.22 rows=34,100,054 width=538) (actual time=1,397.348..1,559.733 rows=153,343 loops=1)

  • Merge Cond: ((all_key.useraccount)::text = (_user_info.account)::text)
73. 206.867 702.887 ↓ 2.9 167,637 1

Sort (cost=8,486.18..8,630.89 rows=57,885 width=272) (actual time=666.937..702.887 rows=167,637 loops=1)

  • Sort Key: all_key.useraccount
  • Sort Method: quicksort Memory: 29,719kB
74. 496.020 496.020 ↓ 2.9 167,638 1

CTE Scan on all_key (cost=0.00..3,907.21 rows=57,885 width=272) (actual time=0.040..496.020 rows=167,638 loops=1)

  • Filter: ((expireddate)::text > (authtime)::text)
  • Rows Removed by Filter: 290
75. 104.358 759.919 ↓ 1.5 176,750 1

Sort (cost=26,223.81..26,518.36 rows=117,820 width=12) (actual time=730.360..759.919 rows=176,750 loops=1)

  • Sort Key: _user_info.account
  • Sort Method: quicksort Memory: 8,718kB
76. 18.446 655.561 ↓ 1.0 121,184 1

Subquery Scan on _user_info (cost=11,586.90..16,299.70 rows=117,820 width=12) (actual time=581.108..655.561 rows=121,184 loops=1)

77. 172.986 637.115 ↓ 1.0 121,184 1

HashAggregate (cost=11,586.90..15,121.50 rows=117,820 width=274) (actual time=581.106..637.115 rows=121,184 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)
78. 262.916 464.129 ↓ 1.0 121,184 1

HashAggregate (cost=5,695.90..9,230.50 rows=117,820 width=274) (actual time=308.014..464.129 rows=121,184 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)
79. 97.538 201.213 ↓ 1.0 121,184 1

Hash Full Join (cost=3,482.87..3,634.05 rows=117,820 width=356) (actual time=102.944..201.213 rows=121,184 loops=1)

  • Hash Cond: ((bk_1.certid)::text = (ac_1.userid)::text)
  • Filter: (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 IS NOT NULL)
80. 0.838 0.838 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.007..0.838 rows=3,876 loops=1)

81. 47.798 102.837 ↑ 1.0 118,400 1

Hash (cost=2,002.72..2,002.72 rows=118,412 width=124) (actual time=102.837..102.837 rows=118,400 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 18,650kB
82. 55.039 55.039 ↑ 1.0 118,400 1

Seq Scan on appuseraccount ac_1 (cost=0.00..2,002.72 rows=118,412 width=124) (actual time=0.022..55.039 rows=118,400 loops=1)

83. 1.475 968.560 ↑ 18.4 9,253 1

Subquery Scan on *SELECT* 2 (cost=34,709.99..39,832.87 rows=170,500 width=214) (actual time=901.369..968.560 rows=9,253 loops=1)

84. 31.962 967.085 ↑ 18.4 9,253 1

Merge Join (cost=34,709.99..38,127.87 rows=170,500 width=538) (actual time=901.367..967.085 rows=9,253 loops=1)

  • Merge Cond: (((all_key_1.useraccount)::text = (_user_info_1.bluekeyaccount)::text) AND ((all_key_1.lockid)::text = (_user_info_1.lockid)::text))
85. 201.151 253.441 ↓ 2.9 167,635 1

Sort (cost=8,486.18..8,630.89 rows=57,885 width=272) (actual time=220.986..253.441 rows=167,635 loops=1)

  • Sort Key: all_key_1.useraccount, all_key_1.lockid
  • Sort Method: quicksort Memory: 29,719kB
86. 52.290 52.290 ↓ 2.9 167,638 1

CTE Scan on all_key all_key_1 (cost=0.00..3,907.21 rows=57,885 width=272) (actual time=0.005..52.290 rows=167,638 loops=1)

  • Filter: ((expireddate)::text > (authtime)::text)
  • Rows Removed by Filter: 290
87. 26.592 681.682 ↑ 12.7 9,266 1

Sort (cost=26,223.81..26,518.36 rows=117,820 width=38) (actual time=680.371..681.682 rows=9,266 loops=1)

  • Sort Key: _user_info_1.bluekeyaccount, _user_info_1.lockid
  • Sort Method: quicksort Memory: 7,058kB
88. 18.971 655.090 ↓ 1.0 121,184 1

Subquery Scan on _user_info_1 (cost=11,586.90..16,299.70 rows=117,820 width=38) (actual time=581.086..655.090 rows=121,184 loops=1)

89. 171.101 636.119 ↓ 1.0 121,184 1

HashAggregate (cost=11,586.90..15,121.50 rows=117,820 width=274) (actual time=581.085..636.119 rows=121,184 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_2.certid, ac_2.userid, ac_2.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_2.certid, ac_2.userid, ac_2.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_2.certid, ac_2.userid, ac_2.account)) END, ac_2.account, CASE WHEN (substr(((COALESCE(bk_2.certid, ac_2.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_2.certid, ac_2.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_2.certid, ac_2.userid)) END, max((COALESCE(ac_2.username, bk_2.username))::text), max((COALESCE(ac_2.headpicture, bk_2.certfrontpic))::text), bk_2.lockid, bk_2.bluekeyaccount, max((ac_2.authstate)::text)
90. 263.892 465.018 ↓ 1.0 121,184 1

HashAggregate (cost=5,695.90..9,230.50 rows=117,820 width=274) (actual time=307.121..465.018 rows=121,184 loops=1)

  • Group Key: COALESCE(bk_2.certid, ac_2.userid, ac_2.account), bk_2.bluekeyaccount, ac_2.account, COALESCE(bk_2.certid, ac_2.userid)
91. 97.383 201.126 ↓ 1.0 121,184 1

Hash Full Join (cost=3,482.87..3,634.05 rows=117,820 width=356) (actual time=102.985..201.126 rows=121,184 loops=1)

  • Hash Cond: ((bk_2.certid)::text = (ac_2.userid)::text)
  • Filter: (CASE WHEN (substr((COALESCE(bk_2.certid, ac_2.userid, ac_2.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_2.certid, ac_2.userid, ac_2.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_2.certid, ac_2.userid, ac_2.account) END IS NOT NULL)
92. 0.865 0.865 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_2 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.010..0.865 rows=3,876 loops=1)

93. 47.020 102.878 ↑ 1.0 118,400 1

Hash (cost=2,002.72..2,002.72 rows=118,412 width=124) (actual time=102.878..102.878 rows=118,400 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 18,650kB
94. 55.858 55.858 ↑ 1.0 118,400 1

Seq Scan on appuseraccount ac_2 (cost=0.00..2,002.72 rows=118,412 width=124) (actual time=0.017..55.858 rows=118,400 loops=1)

95. 20.015 169.504 ↑ 1.1 50,224 1

Hash (cost=3,941.40..3,941.40 rows=54,105 width=99) (actual time=169.504..169.504 rows=50,224 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 7,018kB
96. 20.873 149.489 ↑ 1.1 50,224 1

Hash Left Join (cost=2,317.42..3,941.40 rows=54,105 width=99) (actual time=75.452..149.489 rows=50,224 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
97. 41.882 126.870 ↑ 1.1 50,224 1

Hash Join (cost=2,239.94..3,123.15 rows=54,105 width=111) (actual time=73.690..126.870 rows=50,224 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
98. 11.423 11.423 ↑ 1.0 52,796 1

Seq Scan on lockinfo li (cost=0.00..740.96 rows=54,186 width=51) (actual time=0.015..11.423 rows=52,796 loops=1)

99. 32.515 73.565 ↑ 1.0 77,830 1

Hash (cost=1,266.75..1,266.75 rows=77,855 width=126) (actual time=73.564..73.565 rows=77,830 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 13,175kB
100. 41.050 41.050 ↑ 1.0 77,830 1

Seq Scan on houseinfo hi (cost=0.00..1,266.75 rows=77,855 width=126) (actual time=0.008..41.050 rows=77,830 loops=1)

101. 0.367 1.746 ↓ 1.0 1,639 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
102. 0.528 1.379 ↓ 1.0 1,639 1

Hash Right Join (cost=38.98..57.10 rows=1,630 width=12) (actual time=0.710..1.379 rows=1,639 loops=1)

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

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

104. 0.368 0.692 ↓ 1.0 1,632 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
105. 0.324 0.324 ↓ 1.0 1,632 1

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

106. 0.018 2,958.716 ↓ 23.0 23 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=2,958.716..2,958.716 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
107. 2,958.698 2,958.698 ↓ 23.0 23 1

CTE Scan on new_people (cost=0.00..0.02 rows=1 width=64) (actual time=2,926.759..2,958.698 rows=23 loops=1)

  • Filter: ((bz)::text IS NOT NULL)