explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d4mv

Settings
# exclusive inclusive rows x rows loops node
1. 24.248 4,857.458 ↓ 4.0 4 1

Nested Loop Left Join (cost=229,637.10..232,759.84 rows=1 width=176) (actual time=4,722.674..4,857.458 rows=4 loops=1)

  • Join Filter: (((COALESCE(bk_4.certid, ac_4.userid, ac_4.account)))::text = (_house_user.bz)::text)
  • Rows Removed by Join Filter: 186905
2. 3.673 2,403.274 ↓ 4.0 4 1

Merge Join (cost=109,508.31..112,630.90 rows=1 width=136) (actual time=2,348.690..2,403.274 rows=4 loops=1)

  • Merge Cond: ((_house_user.bz)::text = ((COALESCE(bk_3.certid, ac_3.userid, ac_3.account)))::text)
3. 2.821 2,043.993 ↓ 4.0 4 1

Merge Join (cost=92,426.59..92,795.65 rows=1 width=136) (actual time=2,015.534..2,043.993 rows=4 loops=1)

  • Merge Cond: ((_house_user.bz)::text = (_user_info.bz)::text)
4. 25.950 1,611.777 ↓ 4.0 4 1

GroupAggregate (cost=67,040.14..67,040.21 rows=1 width=48) (actual time=1,588.222..1,611.777 rows=4 loops=1)

  • Group Key: _house_user.bz
  • Filter: (count(DISTINCT _house_user.lockid) FILTER (WHERE ((COALESCE(_house_user.stoptime, _house_user.expireddate))::date <= CURRENT_DATE)) >= 5)
  • Rows Removed by Filter: 8298
5. 9.682 1,585.827 ↓ 4,670.5 9,341 1

Sort (cost=67,040.14..67,040.15 rows=2 width=141) (actual time=1,584.427..1,585.827 rows=9,341 loops=1)

  • Sort Key: _house_user.bz
  • Sort Method: quicksort Memory: 1698kB
6. 1.441 1,576.145 ↓ 4,670.5 9,341 1

Subquery Scan on _house_user (cost=66,651.33..67,040.13 rows=2 width=141) (actual time=1,475.550..1,576.145 rows=9,341 loops=1)

7. 8.706 1,574.704 ↓ 4,670.5 9,341 1

Hash Left Join (cost=66,651.33..67,040.11 rows=2 width=2,217) (actual time=1,475.549..1,574.704 rows=9,341 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 ~~ '330522%'::text)
  • Rows Removed by Filter: 808
8.          

CTE all_key

9. 19.084 226.096 ↑ 1.0 162,589 1

Append (cost=0.00..7,437.64 rows=163,255 width=285) (actual time=0.017..226.096 rows=162,589 loops=1)

10. 159.227 159.227 ↑ 1.0 99,066 1

Seq Scan on lockkeyinfo lk (cost=0.00..5,109.66 rows=99,555 width=290) (actual time=0.016..159.227 rows=99,066 loops=1)

11. 47.785 47.785 ↑ 1.0 63,523 1

Seq Scan on keyhistoryinfo khi (cost=0.00..1,511.70 rows=63,700 width=278) (actual time=0.010..47.785 rows=63,523 loops=1)

12. 4.667 1,565.812 ↓ 27.7 10,149 1

Hash Left Join (cost=59,201.11..59,588.91 rows=366 width=169) (actual time=1,475.352..1,565.812 rows=10,149 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
13. 11.045 1,560.648 ↓ 27.7 10,149 1

Nested Loop (cost=59,174.65..59,557.44 rows=366 width=153) (actual time=1,474.846..1,560.648 rows=10,149 loops=1)

14. 7.240 1,518.586 ↓ 28.2 10,339 1

Nested Loop (cost=59,174.23..59,394.68 rows=367 width=172) (actual time=1,474.831..1,518.586 rows=10,339 loops=1)

15. 15.553 1,480.329 ↓ 28.2 10,339 1

HashAggregate (cost=59,173.82..59,177.49 rows=367 width=538) (actual time=1,474.788..1,480.329 rows=10,339 loops=1)

  • Group Key: all_key.lockid, all_key.keyid, all_key.useraccount, all_key.keytype, all_key.valid_state, all_key.keystate, all_key.authtime, all_key.expireddate, all_key.stoptime, all_key.td_time, (COALESCE(bk.certid, ac.userid, ac.account)), all_key.ly
16. 1.087 1,464.776 ↓ 28.9 10,614 1

Append (cost=26,796.43..59,162.81 rows=367 width=538) (actual time=853.802..1,464.776 rows=10,614 loops=1)

17. 17.636 914.429 ↓ 27.2 9,914 1

Hash Join (cost=26,796.43..29,443.72 rows=365 width=538) (actual time=853.801..914.429 rows=9,914 loops=1)

  • Hash Cond: ((ac.account)::text = (all_key.useraccount)::text)
18. 27.340 346.162 ↓ 1.0 75,337 1

Unique (cost=17,001.12..18,642.00 rows=72,928 width=209) (actual time=303.155..346.162 rows=75,337 loops=1)

19. 82.338 318.822 ↓ 1.0 75,337 1

Sort (cost=17,001.12..17,183.44 rows=72,928 width=209) (actual time=303.153..318.822 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk.certid, ac.userid, ac.account)), ac.account, (COALESCE(bk.certid, ac.userid)), (max((COALESCE(ac.username, bk.username))::text)), (max((COALESCE(ac.headpicture, bk.certfrontpic))::text)), bk.lockid, bk.bluekeyaccount, (max((ac.authstate)::text))
  • Sort Method: external merge Disk: 11048kB
20. 60.920 236.484 ↓ 1.0 75,337 1

GroupAggregate (cost=8,495.42..10,683.26 rows=72,928 width=209) (actual time=155.266..236.484 rows=75,337 loops=1)

  • Group Key: (COALESCE(bk.certid, ac.userid, ac.account)), bk.bluekeyaccount, ac.account, (COALESCE(bk.certid, ac.userid))
21. 118.393 175.564 ↓ 1.0 75,337 1

Sort (cost=8,495.42..8,677.74 rows=72,928 width=319) (actual time=155.253..175.564 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk.certid, ac.userid, ac.account)), bk.bluekeyaccount, ac.account, (COALESCE(bk.certid, ac.userid))
  • Sort Method: external merge Disk: 11752kB
22. 33.487 57.171 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=72,928 width=319) (actual time=1.585..57.171 rows=75,337 loops=1)

  • Hash Cond: ((ac.userid)::text = (bk.certid)::text)
  • Filter: (COALESCE(bk.certid, ac.userid, ac.account) IS NOT NULL)
23. 22.127 22.127 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.016..22.127 rows=73,586 loops=1)

24. 0.744 1.557 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.557..1.557 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
25. 0.813 0.813 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.014..0.813 rows=2,593 loops=1)

26. 5.713 550.631 ↓ 11,392.0 11,392 1

Hash (cost=9,795.30..9,795.30 rows=1 width=506) (actual time=550.631..550.631 rows=11,392 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2249kB
27. 544.918 544.918 ↓ 11,392.0 11,392 1

CTE Scan on all_key (cost=0.00..9,795.30 rows=1 width=506) (actual time=3.086..544.918 rows=11,392 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255) AND (keystate = 0) AND ((authtime)::date <= CURRENT_DATE) AND ((authtime)::date <= CURRENT_DATE) AND ((CURRENT_DATE - (authtime)::date) <= 30))
  • Rows Removed by Filter: 151197
28. 5.672 549.260 ↓ 350.0 700 1

Hash Join (cost=26,796.44..29,713.58 rows=2 width=538) (actual time=502.869..549.260 rows=700 loops=1)

  • Hash Cond: (((bk_1.bluekeyaccount)::text = (all_key_1.useraccount)::text) AND ((bk_1.lockid)::text = (all_key_1.lockid)::text))
29. 27.104 346.275 ↓ 1.0 75,337 1

Unique (cost=17,001.12..18,642.00 rows=72,928 width=209) (actual time=304.411..346.275 rows=75,337 loops=1)

30. 85.615 319.171 ↓ 1.0 75,337 1

Sort (cost=17,001.12..17,183.44 rows=72,928 width=209) (actual time=304.409..319.171 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_1.certid, ac_1.userid, ac_1.account)), ac_1.account, (COALESCE(bk_1.certid, ac_1.userid)), (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))
  • Sort Method: external merge Disk: 11048kB
31. 58.116 233.556 ↓ 1.0 75,337 1

GroupAggregate (cost=8,495.42..10,683.26 rows=72,928 width=209) (actual time=156.628..233.556 rows=75,337 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))
32. 119.320 175.440 ↓ 1.0 75,337 1

Sort (cost=8,495.42..8,677.74 rows=72,928 width=319) (actual time=156.615..175.440 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_1.certid, ac_1.userid, ac_1.account)), bk_1.bluekeyaccount, ac_1.account, (COALESCE(bk_1.certid, ac_1.userid))
  • Sort Method: external merge Disk: 11752kB
33. 32.594 56.120 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=72,928 width=319) (actual time=1.454..56.120 rows=75,337 loops=1)

  • Hash Cond: ((ac_1.userid)::text = (bk_1.certid)::text)
  • Filter: (COALESCE(bk_1.certid, ac_1.userid, ac_1.account) IS NOT NULL)
34. 22.093 22.093 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac_1 (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.010..22.093 rows=73,586 loops=1)

35. 0.733 1.433 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.433..1.433 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
36. 0.700 0.700 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.013..0.700 rows=2,593 loops=1)

37. 4.910 197.313 ↓ 11,392.0 11,392 1

Hash (cost=9,795.30..9,795.30 rows=1 width=506) (actual time=197.313..197.313 rows=11,392 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2249kB
38. 192.403 192.403 ↓ 11,392.0 11,392 1

CTE Scan on all_key all_key_1 (cost=0.00..9,795.30 rows=1 width=506) (actual time=1.074..192.403 rows=11,392 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255) AND (keystate = 0) AND ((authtime)::date <= CURRENT_DATE) AND ((authtime)::date <= CURRENT_DATE) AND ((CURRENT_DATE - (authtime)::date) <= 30))
  • Rows Removed by Filter: 151197
39. 31.017 31.017 ↑ 1.0 1 10,339

Index Scan using "pk_lockinfo_1564730230_130 " on lockinfo li (cost=0.41..0.58 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=10,339)

  • Index Cond: ((lockid)::text = (all_key.lockid)::text)
40. 31.017 31.017 ↑ 1.0 1 10,339

Index Scan using idx_index_hous_houseinfo_1564730207_116 on houseinfo hi (cost=0.41..0.44 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=10,339)

  • Index Cond: ((houseid)::text = (li.houseid)::text)
41. 0.096 0.497 ↓ 1.0 532 1

Hash (cost=19.85..19.85 rows=529 width=28) (actual time=0.497..0.497 rows=532 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
42. 0.171 0.401 ↓ 1.0 532 1

Hash Right Join (cost=12.70..19.85 rows=529 width=28) (actual time=0.184..0.401 rows=532 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
43. 0.057 0.057 ↑ 1.0 526 1

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

44. 0.086 0.173 ↑ 1.0 529 1

Hash (cost=6.09..6.09 rows=529 width=28) (actual time=0.173..0.173 rows=529 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
45. 0.087 0.087 ↑ 1.0 529 1

Seq Scan on "行政区划代码" xz (cost=0.00..6.09 rows=529 width=28) (actual time=0.006..0.087 rows=529 loops=1)

46. 0.095 0.186 ↑ 1.0 528 1

Hash (cost=5.98..5.98 rows=528 width=44) (actual time=0.186..0.186 rows=528 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
47. 0.091 0.091 ↑ 1.0 528 1

Seq Scan on "管理单位代码" gl (cost=0.00..5.98 rows=528 width=44) (actual time=0.004..0.091 rows=528 loops=1)

48. 5.744 429.395 ↓ 119.2 23,843 1

Unique (cost=25,386.45..25,752.92 rows=200 width=128) (actual time=419.084..429.395 rows=23,843 loops=1)

49. 60.010 423.651 ↑ 3.0 24,497 1

Sort (cost=25,386.45..25,569.68 rows=73,294 width=128) (actual time=419.082..423.651 rows=24,497 loops=1)

  • Sort Key: _user_info.bz, _user_info.authstate
  • Sort Method: external merge Disk: 8760kB
50. 13.141 363.641 ↓ 1.0 75,337 1

Subquery Scan on _user_info (cost=17,081.72..19,463.77 rows=73,294 width=128) (actual time=309.890..363.641 rows=75,337 loops=1)

51. 23.429 350.500 ↓ 1.0 75,337 1

Unique (cost=17,081.72..18,730.83 rows=73,294 width=209) (actual time=309.887..350.500 rows=75,337 loops=1)

52. 89.121 327.071 ↓ 1.0 75,337 1

Sort (cost=17,081.72..17,264.95 rows=73,294 width=209) (actual time=309.885..327.071 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_2.certid, ac_2.userid, ac_2.account)), ac_2.account, (COALESCE(bk_2.certid, ac_2.userid)), (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))
  • Sort Method: external merge Disk: 11048kB
53. 60.553 237.950 ↓ 1.0 75,337 1

GroupAggregate (cost=8,530.63..10,729.45 rows=73,294 width=209) (actual time=158.522..237.950 rows=75,337 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))
54. 124.218 177.397 ↓ 1.0 75,337 1

Sort (cost=8,530.63..8,713.86 rows=73,294 width=319) (actual time=158.507..177.397 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_2.certid, ac_2.userid, ac_2.account)), bk_2.bluekeyaccount, ac_2.account, (COALESCE(bk_2.certid, ac_2.userid))
  • Sort Method: external merge Disk: 11752kB
55. 29.838 53.179 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=73,294 width=319) (actual time=1.500..53.179 rows=75,337 loops=1)

  • Hash Cond: ((ac_2.userid)::text = (bk_2.certid)::text)
56. 21.865 21.865 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac_2 (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.015..21.865 rows=73,586 loops=1)

57. 0.768 1.476 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.476..1.476 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
58. 0.708 0.708 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk_2 (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.009..0.708 rows=2,593 loops=1)

59. 45.199 355.608 ↓ 119.2 23,843 1

GroupAggregate (cost=17,081.72..19,832.74 rows=200 width=64) (actual time=293.099..355.608 rows=23,843 loops=1)

  • Group Key: (COALESCE(bk_3.certid, ac_3.userid, ac_3.account))
60. 10.694 310.409 ↑ 3.0 24,498 1

Unique (cost=17,081.72..18,730.83 rows=73,294 width=209) (actual time=293.071..310.409 rows=24,498 loops=1)

61. 73.958 299.715 ↑ 3.0 24,498 1

Sort (cost=17,081.72..17,264.95 rows=73,294 width=209) (actual time=293.069..299.715 rows=24,498 loops=1)

  • Sort Key: (COALESCE(bk_3.certid, ac_3.userid, ac_3.account)), ac_3.account, (COALESCE(bk_3.certid, ac_3.userid)), (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))
  • Sort Method: external merge Disk: 11048kB
62. 58.160 225.757 ↓ 1.0 75,337 1

GroupAggregate (cost=8,530.63..10,729.45 rows=73,294 width=209) (actual time=149.042..225.757 rows=75,337 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))
63. 116.019 167.597 ↓ 1.0 75,337 1

Sort (cost=8,530.63..8,713.86 rows=73,294 width=319) (actual time=149.029..167.597 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_3.certid, ac_3.userid, ac_3.account)), bk_3.bluekeyaccount, ac_3.account, (COALESCE(bk_3.certid, ac_3.userid))
  • Sort Method: external merge Disk: 11752kB
64. 29.025 51.578 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=73,294 width=319) (actual time=1.451..51.578 rows=75,337 loops=1)

  • Hash Cond: ((ac_3.userid)::text = (bk_3.certid)::text)
65. 21.125 21.125 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac_3 (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.012..21.125 rows=73,586 loops=1)

66. 0.730 1.428 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.428..1.428 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
67. 0.698 0.698 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk_3 (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.014..0.698 rows=2,593 loops=1)

68. 53.276 2,429.936 ↓ 9,345.4 46,727 4

Unique (cost=120,128.79..120,128.82 rows=5 width=215) (actual time=586.009..607.484 rows=46,727 loops=4)

69. 102.106 2,376.660 ↓ 12,039.2 60,196 4

Sort (cost=120,128.79..120,128.81 rows=5 width=215) (actual time=586.008..594.165 rows=60,196 loops=4)

  • Sort Key: (COALESCE(bk_4.certid, ac_4.userid, ac_4.account)) DESC, ((opendoor_summary_mrel._0)::timestamp(0) without time zone) DESC NULLS LAST
  • Sort Method: quicksort Memory: 15172kB
70. 53.914 2,274.554 ↓ 14,081.6 70,408 1

Hash Join (cost=104,163.01..120,128.74 rows=5 width=215) (actual time=2,034.118..2,274.554 rows=70,408 loops=1)

  • Hash Cond: (((li_1.lockid)::text = opendoor_summary_mrel.lockid) AND ((all_key_2.useraccount)::text = opendoor_summary_mrel.userid))
71. 68.493 2,066.176 ↑ 2.7 104,192 1

Hash Right Join (cost=97,384.84..109,052.34 rows=281,850 width=1,700) (actual time=1,879.480..2,066.176 rows=104,192 loops=1)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
72.          

CTE all_key

73. 17.087 219.957 ↑ 1.0 162,589 1

Append (cost=0.00..7,437.64 rows=163,255 width=285) (actual time=0.026..219.957 rows=162,589 loops=1)

74. 155.872 155.872 ↑ 1.0 99,066 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..5,109.66 rows=99,555 width=290) (actual time=0.025..155.872 rows=99,066 loops=1)

75. 46.998 46.998 ↑ 1.0 63,523 1

Seq Scan on keyhistoryinfo khi_1 (cost=0.00..1,511.70 rows=63,700 width=278) (actual time=0.011..46.998 rows=63,523 loops=1)

76. 50.060 1,938.499 ↑ 2.1 93,720 1

Hash Right Join (cost=87,810.85..96,757.22 rows=198,256 width=140) (actual time=1,820.108..1,938.499 rows=93,720 loops=1)

  • Hash Cond: ((all_key_2.lockid)::text = (li_1.lockid)::text)
77. 40.171 1,868.962 ↑ 2.2 88,330 1

Unique (cost=86,897.53..93,340.85 rows=198,256 width=538) (actual time=1,800.433..1,868.962 rows=88,330 loops=1)

78. 193.219 1,828.791 ↑ 2.2 90,164 1

Sort (cost=86,897.53..87,393.17 rows=198,256 width=538) (actual time=1,800.431..1,828.791 rows=90,164 loops=1)

  • Sort Key: all_key_2.lockid, all_key_2.keyid, all_key_2.useraccount, all_key_2.keytype, all_key_2.valid_state, all_key_2.keystate, all_key_2.authtime, all_key_2.expireddate, all_key_2.stoptime, all_key_2.td_time, (COALESCE(bk_4.certid, ac_4.userid, ac_4.account)), all_key_2.ly
  • Sort Method: external merge Disk: 16552kB
79. 9.118 1,635.572 ↑ 2.2 90,164 1

Append (cost=22,721.81..66,704.58 rows=198,256 width=538) (actual time=825.315..1,635.572 rows=90,164 loops=1)

80. 78.511 949.896 ↑ 2.3 85,479 1

Hash Join (cost=22,721.81..32,169.63 rows=197,270 width=538) (actual time=825.314..949.896 rows=85,479 loops=1)

  • Hash Cond: ((ac_4.account)::text = (all_key_2.useraccount)::text)
81. 29.833 341.279 ↓ 1.0 75,337 1

Unique (cost=17,001.12..18,642.00 rows=72,928 width=209) (actual time=295.129..341.279 rows=75,337 loops=1)

82. 83.482 311.446 ↓ 1.0 75,337 1

Sort (cost=17,001.12..17,183.44 rows=72,928 width=209) (actual time=295.127..311.446 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_4.certid, ac_4.userid, ac_4.account)), ac_4.account, (COALESCE(bk_4.certid, ac_4.userid)), (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))
  • Sort Method: external merge Disk: 11048kB
83. 57.171 227.964 ↓ 1.0 75,337 1

GroupAggregate (cost=8,495.42..10,683.26 rows=72,928 width=209) (actual time=152.424..227.964 rows=75,337 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))
84. 116.311 170.793 ↓ 1.0 75,337 1

Sort (cost=8,495.42..8,677.74 rows=72,928 width=319) (actual time=152.410..170.793 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_4.certid, ac_4.userid, ac_4.account)), bk_4.bluekeyaccount, ac_4.account, (COALESCE(bk_4.certid, ac_4.userid))
  • Sort Method: external merge Disk: 11752kB
85. 31.835 54.482 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=72,928 width=319) (actual time=1.440..54.482 rows=75,337 loops=1)

  • Hash Cond: ((ac_4.userid)::text = (bk_4.certid)::text)
  • Filter: (COALESCE(bk_4.certid, ac_4.userid, ac_4.account) IS NOT NULL)
86. 21.231 21.231 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac_4 (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.013..21.231 rows=73,586 loops=1)

87. 0.732 1.416 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.416..1.416 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
88. 0.684 0.684 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk_4 (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.007..0.684 rows=2,593 loops=1)

89. 60.936 530.106 ↓ 176.0 95,202 1

Hash (cost=5,713.93..5,713.93 rows=541 width=506) (actual time=530.105..530.106 rows=95,202 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 15361kB
90. 469.170 469.170 ↓ 176.0 95,202 1

CTE Scan on all_key all_key_2 (cost=0.00..5,713.93 rows=541 width=506) (actual time=0.036..469.170 rows=95,202 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255) AND ((authtime)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 67387
91. 14.232 676.558 ↓ 4.8 4,685 1

Merge Join (cost=31,000.23..31,561.11 rows=986 width=538) (actual time=640.953..676.558 rows=4,685 loops=1)

  • Merge Cond: (((all_key_3.useraccount)::text = (_user_info_1.bluekeyaccount)::text) AND ((all_key_3.lockid)::text = (_user_info_1.lockid)::text))
92. 143.320 276.455 ↓ 176.0 95,199 1

Sort (cost=5,738.49..5,739.84 rows=541 width=506) (actual time=255.641..276.455 rows=95,199 loops=1)

  • Sort Key: all_key_3.useraccount, all_key_3.lockid
  • Sort Method: external merge Disk: 15776kB
93. 133.135 133.135 ↓ 176.0 95,202 1

CTE Scan on all_key all_key_3 (cost=0.00..5,713.93 rows=541 width=506) (actual time=0.043..133.135 rows=95,202 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255) AND ((authtime)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 67387
94. 15.711 385.871 ↑ 15.5 4,713 1

Sort (cost=25,261.74..25,444.06 rows=72,928 width=70) (actual time=385.303..385.871 rows=4,713 loops=1)

  • Sort Key: _user_info_1.bluekeyaccount, _user_info_1.lockid
  • Sort Method: quicksort Memory: 8996kB
95. 14.490 370.160 ↓ 1.0 75,337 1

Subquery Scan on _user_info_1 (cost=17,001.12..19,371.28 rows=72,928 width=70) (actual time=311.587..370.160 rows=75,337 loops=1)

96. 27.504 355.670 ↓ 1.0 75,337 1

Unique (cost=17,001.12..18,642.00 rows=72,928 width=209) (actual time=311.583..355.670 rows=75,337 loops=1)

97. 88.617 328.166 ↓ 1.0 75,337 1

Sort (cost=17,001.12..17,183.44 rows=72,928 width=209) (actual time=311.581..328.166 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_5.certid, ac_5.userid, ac_5.account)), ac_5.account, (COALESCE(bk_5.certid, ac_5.userid)), (max((COALESCE(ac_5.username, bk_5.username))::text)), (max((COALESCE(ac_5.headpicture, bk_5.certfrontpic))::text)), bk_5.lockid, bk_5.bluekeyaccount, (max((ac_5.authstate)::text))
  • Sort Method: external merge Disk: 11048kB
98. 62.025 239.549 ↓ 1.0 75,337 1

GroupAggregate (cost=8,495.42..10,683.26 rows=72,928 width=209) (actual time=158.369..239.549 rows=75,337 loops=1)

  • Group Key: (COALESCE(bk_5.certid, ac_5.userid, ac_5.account)), bk_5.bluekeyaccount, ac_5.account, (COALESCE(bk_5.certid, ac_5.userid))
99. 123.343 177.524 ↓ 1.0 75,337 1

Sort (cost=8,495.42..8,677.74 rows=72,928 width=319) (actual time=158.355..177.524 rows=75,337 loops=1)

  • Sort Key: (COALESCE(bk_5.certid, ac_5.userid, ac_5.account)), bk_5.bluekeyaccount, ac_5.account, (COALESCE(bk_5.certid, ac_5.userid))
  • Sort Method: external merge Disk: 11752kB
100. 31.698 54.181 ↓ 1.0 75,337 1

Hash Full Join (cost=67.47..1,992.36 rows=72,928 width=319) (actual time=1.427..54.181 rows=75,337 loops=1)

  • Hash Cond: ((ac_5.userid)::text = (bk_5.certid)::text)
  • Filter: (COALESCE(bk_5.certid, ac_5.userid, ac_5.account) IS NOT NULL)
101. 21.136 21.136 ↓ 1.0 73,586 1

Seq Scan on appuseraccount ac_5 (cost=0.00..1,256.64 rows=73,294 width=123) (actual time=0.058..21.136 rows=73,586 loops=1)

102. 0.669 1.347 ↓ 1.0 2,593 1

Hash (cost=35.60..35.60 rows=2,550 width=168) (actual time=1.347..1.347 rows=2,593 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 524kB
103. 0.678 0.678 ↓ 1.0 2,593 1

Seq Scan on bluekeyuserinfo bk_5 (cost=0.00..35.60 rows=2,550 width=168) (actual time=0.015..0.678 rows=2,593 loops=1)

104. 8.677 19.477 ↑ 1.0 34,361 1

Hash (cost=478.42..478.42 rows=34,792 width=50) (actual time=19.477..19.477 rows=34,361 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3319kB
105. 10.800 10.800 ↑ 1.0 34,361 1

Seq Scan on lockinfo li_1 (cost=0.00..478.42 rows=34,792 width=50) (actual time=0.010..10.800 rows=34,361 loops=1)

106. 17.018 59.184 ↑ 1.0 49,364 1

Hash (cost=1,518.07..1,518.07 rows=49,462 width=110) (actual time=59.184..59.184 rows=49,364 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 7408kB
107. 29.914 42.166 ↑ 1.0 49,364 1

Hash Left Join (cost=26.47..1,518.07 rows=49,462 width=110) (actual time=0.602..42.166 rows=49,364 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
108. 11.664 11.664 ↑ 1.0 49,364 1

Seq Scan on houseinfo hi_1 (cost=0.00..815.82 rows=49,462 width=122) (actual time=0.007..11.664 rows=49,364 loops=1)

109. 0.114 0.588 ↓ 1.0 532 1

Hash (cost=19.85..19.85 rows=529 width=12) (actual time=0.588..0.588 rows=532 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
110. 0.193 0.474 ↓ 1.0 532 1

Hash Right Join (cost=12.70..19.85 rows=529 width=12) (actual time=0.231..0.474 rows=532 loops=1)

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
111. 0.067 0.067 ↑ 1.0 526 1

Seq Scan on "行政区划与管理单位代码映射" ys_1 (cost=0.00..5.76 rows=526 width=32) (actual time=0.005..0.067 rows=526 loops=1)

112. 0.112 0.214 ↑ 1.0 529 1

Hash (cost=6.09..6.09 rows=529 width=28) (actual time=0.214..0.214 rows=529 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
113. 0.102 0.102 ↑ 1.0 529 1

Seq Scan on "行政区划代码" xz_1 (cost=0.00..6.09 rows=529 width=28) (actual time=0.006..0.102 rows=529 loops=1)

114. 46.693 154.464 ↓ 1.0 147,599 1

Hash (cost=4,633.67..4,633.67 rows=142,967 width=38) (actual time=154.464..154.464 rows=147,599 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 12409kB
115. 107.771 107.771 ↓ 1.0 147,804 1

Seq Scan on opendoor_summary_mrel (cost=0.00..4,633.67 rows=142,967 width=38) (actual time=0.023..107.771 rows=147,804 loops=1)