explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4CwH

Settings
# exclusive inclusive rows x rows loops node
1. 54.470 3,289.967 ↑ 3.9 141,105 1

Subquery Scan on a (cost=110,151.69..178,139.50 rows=544,907 width=992) (actual time=2,460.293..3,289.967 rows=141,105 loops=1)

2.          

CTE all_key

3. 23.040 274.302 ↑ 1.0 181,783 1

Append (cost=0.00..8,270.13 rows=183,800 width=285) (actual time=0.034..274.302 rows=181,783 loops=1)

4. 188.937 188.937 ↑ 1.0 108,170 1

Seq Scan on lockkeyinfo lk (cost=0.00..5,601.95 rows=110,201 width=290) (actual time=0.034..188.937 rows=108,170 loops=1)

5. 62.325 62.325 ↓ 1.0 73,613 1

Seq Scan on keyhistoryinfo khi (cost=0.00..1,749.18 rows=73,599 width=278) (actual time=0.019..62.325 rows=73,613 loops=1)

6. 537.440 3,235.497 ↑ 3.9 141,105 1

WindowAgg (cost=101,881.56..161,695.77 rows=544,907 width=960) (actual time=2,460.289..3,235.497 rows=141,105 loops=1)

7. 106.521 2,698.057 ↑ 3.9 141,105 1

Merge Left Join (cost=101,881.56..126,276.81 rows=544,907 width=933) (actual time=2,460.246..2,698.057 rows=141,105 loops=1)

  • Merge Cond: ((li.lockid)::text = (all_key.lockid)::text)
8. 75.777 233.976 ↑ 1.0 53,709 1

Sort (cost=7,209.96..7,344.98 rows=54,007 width=493) (actual time=227.443..233.976 rows=53,709 loops=1)

  • Sort Key: li.lockid
  • Sort Method: quicksort Memory: 38418kB
9. 30.164 158.199 ↑ 1.0 53,709 1

Hash Left Join (cost=1,621.57..2,964.78 rows=54,007 width=493) (actual time=72.684..158.199 rows=53,709 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 47.598 125.316 ↑ 1.0 53,709 1

Hash Right Join (cost=1,536.06..2,141.12 rows=54,007 width=372) (actual time=69.950..125.316 rows=53,709 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
11. 7.842 7.842 ↑ 1.0 37,184 1

Seq Scan on lockinfo li (cost=0.00..507.40 rows=37,200 width=70) (actual time=0.011..7.842 rows=37,184 loops=1)

12. 32.367 69.876 ↑ 1.0 53,672 1

Hash (cost=860.97..860.97 rows=54,007 width=333) (actual time=69.875..69.876 rows=53,672 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 16941kB
13. 37.509 37.509 ↑ 1.0 53,672 1

Seq Scan on houseinfo hi (cost=0.00..860.97 rows=54,007 width=333) (actual time=0.011..37.509 rows=53,672 loops=1)

14. 0.607 2.719 ↓ 1.0 1,149 1

Hash (cost=71.34..71.34 rows=1,134 width=133) (actual time=2.719..2.719 rows=1,149 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 198kB
15. 0.459 2.112 ↓ 1.0 1,149 1

Hash Right Join (cost=53.56..71.34 rows=1,134 width=133) (actual time=1.088..2.112 rows=1,149 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
16. 0.439 1.146 ↑ 1.0 1,092 1

Hash Left Join (cost=26.45..41.35 rows=1,092 width=89) (actual time=0.572..1.146 rows=1,092 loops=1)

  • Hash Cond: (ys."管理单位代码_id" = gl.id)
17. 0.146 0.146 ↑ 1.0 1,092 1

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

18. 0.326 0.561 ↑ 1.0 1,100 1

Hash (cost=12.70..12.70 rows=1,100 width=89) (actual time=0.560..0.561 rows=1,100 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 145kB
19. 0.235 0.235 ↑ 1.0 1,100 1

Seq Scan on "管理单位代码" gl (cost=0.00..12.70 rows=1,100 width=89) (actual time=0.006..0.235 rows=1,100 loops=1)

20. 0.286 0.507 ↑ 1.0 1,134 1

Hash (cost=12.94..12.94 rows=1,134 width=76) (actual time=0.507..0.507 rows=1,134 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 139kB
21. 0.221 0.221 ↑ 1.0 1,134 1

Seq Scan on "行政区划代码" xz (cost=0.00..12.94 rows=1,134 width=76) (actual time=0.006..0.221 rows=1,134 loops=1)

22. 26.414 2,357.560 ↑ 3.0 125,393 1

Materialize (cost=94,671.60..111,561.54 rows=375,332 width=538) (actual time=2,232.792..2,357.560 rows=125,393 loops=1)

23. 69.351 2,331.146 ↑ 3.0 125,393 1

Unique (cost=94,671.60..106,869.89 rows=375,332 width=538) (actual time=2,232.785..2,331.146 rows=125,393 loops=1)

24. 296.619 2,261.795 ↑ 2.9 129,360 1

Sort (cost=94,671.60..95,609.93 rows=375,332 width=538) (actual time=2,232.782..2,261.795 rows=129,360 loops=1)

  • Sort 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, _user_info.bz, all_key.ly
  • Sort Method: quicksort Memory: 37440kB
25. 14.980 1,965.176 ↑ 2.9 129,384 1

Append (cost=21,420.11..54,715.17 rows=375,332 width=538) (actual time=1,096.759..1,965.176 rows=129,384 loops=1)

26. 82.381 1,228.850 ↑ 3.0 123,826 1

Merge Join (cost=21,420.11..27,026.65 rows=373,465 width=538) (actual time=1,096.757..1,228.850 rows=123,826 loops=1)

  • Merge Cond: ((all_key.useraccount)::text = (_user_info.account)::text)
27. 169.173 740.751 ↓ 148.1 135,406 1

Sort (cost=6,707.70..6,709.99 rows=914 width=506) (actual time=712.224..740.751 rows=135,406 loops=1)

  • Sort Key: all_key.useraccount
  • Sort Method: quicksort Memory: 41282kB
28. 571.578 571.578 ↓ 148.1 135,407 1

CTE Scan on all_key (cost=0.00..6,662.75 rows=914 width=506) (actual time=0.044..571.578 rows=135,407 loops=1)

  • Filter: ((userrole <> 255) AND (keytype = ANY ('{0,2,1}'::integer[])) AND ((authtime)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 46376
29. 78.084 405.718 ↓ 1.7 137,425 1

Sort (cost=14,712.41..14,916.71 rows=81,721 width=44) (actual time=384.471..405.718 rows=137,425 loops=1)

  • Sort Key: _user_info.account
  • Sort Method: quicksort Memory: 9640kB
30. 12.502 327.634 ↓ 1.0 84,080 1

Subquery Scan on _user_info (cost=6,410.20..8,044.62 rows=81,721 width=44) (actual time=281.926..327.634 rows=84,080 loops=1)

31. 96.690 315.132 ↓ 1.0 84,080 1

HashAggregate (cost=6,410.20..7,227.41 rows=81,721 width=210) (actual time=281.924..315.132 rows=84,080 loops=1)

  • Group 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)
32. 108.332 218.442 ↓ 1.0 84,080 1

HashAggregate (cost=3,958.57..4,775.78 rows=81,721 width=210) (actual time=169.851..218.442 rows=84,080 loops=1)

  • Group Key: COALESCE(bk.certid, ac.userid, ac.account), bk.bluekeyaccount, ac.account, COALESCE(bk.certid, ac.userid)
33. 40.415 110.110 ↓ 1.0 84,080 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=69.038..110.110 rows=84,080 loops=1)

  • Hash Cond: ((bk.certid)::text = (ac.userid)::text)
  • Filter: (COALESCE(bk.certid, ac.userid, ac.account) IS NOT NULL)
34. 0.759 0.759 ↑ 1.0 2,885 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..39.70 rows=2,900 width=168) (actual time=0.008..0.759 rows=2,885 loops=1)

35. 30.357 68.936 ↑ 1.0 82,104 1

Hash (cost=1,373.32..1,373.32 rows=82,132 width=124) (actual time=68.936..68.936 rows=82,104 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 13197kB
36. 38.579 38.579 ↑ 1.0 82,104 1

Seq Scan on appuseraccount ac (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.028..38.579 rows=82,104 loops=1)

37. 24.836 721.346 ↓ 3.0 5,558 1

Merge Join (cost=21,420.11..22,058.54 rows=1,867 width=538) (actual time=668.706..721.346 rows=5,558 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))
38. 169.820 342.203 ↓ 148.1 135,404 1

Sort (cost=6,707.70..6,709.99 rows=914 width=506) (actual time=315.229..342.203 rows=135,404 loops=1)

  • Sort Key: all_key_1.useraccount, all_key_1.lockid
  • Sort Method: quicksort Memory: 41282kB
39. 172.383 172.383 ↓ 148.1 135,407 1

CTE Scan on all_key all_key_1 (cost=0.00..6,662.75 rows=914 width=506) (actual time=0.017..172.383 rows=135,407 loops=1)

  • Filter: ((userrole <> 255) AND (keytype = ANY ('{0,2,1}'::integer[])) AND ((authtime)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 46376
40. 22.863 354.307 ↑ 14.6 5,586 1

Sort (cost=14,712.41..14,916.71 rows=81,721 width=70) (actual time=353.465..354.307 rows=5,586 loops=1)

  • Sort Key: _user_info_1.bluekeyaccount, _user_info_1.lockid
  • Sort Method: quicksort Memory: 9688kB
41. 12.613 331.444 ↓ 1.0 84,080 1

Subquery Scan on _user_info_1 (cost=6,410.20..8,044.62 rows=81,721 width=70) (actual time=285.863..331.444 rows=84,080 loops=1)

42. 103.130 318.831 ↓ 1.0 84,080 1

HashAggregate (cost=6,410.20..7,227.41 rows=81,721 width=210) (actual time=285.862..318.831 rows=84,080 loops=1)

  • Group 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)
43. 107.849 215.701 ↓ 1.0 84,080 1

HashAggregate (cost=3,958.57..4,775.78 rows=81,721 width=210) (actual time=167.714..215.701 rows=84,080 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)
44. 39.294 107.852 ↓ 1.0 84,080 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=68.069..107.852 rows=84,080 loops=1)

  • Hash Cond: ((bk_1.certid)::text = (ac_1.userid)::text)
  • Filter: (COALESCE(bk_1.certid, ac_1.userid, ac_1.account) IS NOT NULL)
45. 0.588 0.588 ↑ 1.0 2,885 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..39.70 rows=2,900 width=168) (actual time=0.006..0.588 rows=2,885 loops=1)

46. 29.369 67.970 ↑ 1.0 82,104 1

Hash (cost=1,373.32..1,373.32 rows=82,132 width=124) (actual time=67.970..67.970 rows=82,104 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 13197kB
47. 38.601 38.601 ↑ 1.0 82,104 1

Seq Scan on appuseraccount ac_1 (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.056..38.601 rows=82,104 loops=1)

Planning time : 2.458 ms
Execution time : 3,310.689 ms