explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p2Xf

Settings
# exclusive inclusive rows x rows loops node
1. 61.520 3,634.305 ↑ 11.2 33,302 1

Sort (cost=244,387.90..245,319.43 rows=372,612 width=369) (actual time=3,628.486..3,634.305 rows=33,302 loops=1)

  • Sort Key: a.lock_createtime DESC
  • Sort Method: quicksort Memory: 18707kB
2. 9.969 3,572.785 ↑ 11.2 33,302 1

Subquery Scan on a (cost=181,117.55..206,268.86 rows=372,612 width=369) (actual time=3,395.546..3,572.785 rows=33,302 loops=1)

3. 150.303 3,562.816 ↑ 11.2 33,302 1

GroupAggregate (cost=181,117.55..201,611.21 rows=372,612 width=337) (actual time=3,395.543..3,562.816 rows=33,302 loops=1)

  • Group Key: hk.detailaddr, hk.housenumber, hk.xzqhdm, hk.xzqhqc, hk.pcsdm, hk.pcsmc, hk.house_createtime, hk.lock_createtime, hk.lockid, hk.ownername, hk.ownerid, hk.ownerphone, hk.longitude, hk.latitude, hk.jwq
4. 463.251 3,412.513 ↑ 3.5 105,469 1

Sort (cost=181,117.55..182,049.08 rows=372,612 width=459) (actual time=3,395.506..3,412.513 rows=105,469 loops=1)

  • Sort Key: hk.detailaddr, hk.housenumber, hk.xzqhdm, hk.xzqhqc, hk.pcsdm, hk.pcsmc, hk.house_createtime, hk.lock_createtime, hk.lockid, hk.ownername, hk.ownerid, hk.ownerphone, hk.longitude, hk.latitude, hk.jwq
  • Sort Method: quicksort Memory: 57232kB
5. 28.403 2,949.262 ↑ 3.5 105,469 1

Subquery Scan on hk (cost=106,133.33..142,197.92 rows=372,612 width=459) (actual time=2,604.087..2,949.262 rows=105,469 loops=1)

6. 48.467 2,920.859 ↑ 3.5 105,469 1

Subquery Scan on a_1 (cost=106,133.33..138,471.80 rows=372,612 width=1,317) (actual time=2,604.085..2,920.859 rows=105,469 loops=1)

  • Filter: (a_1.pcsdm IS NOT NULL)
  • Rows Removed by Filter: 17433
7.          

CTE all_key

8. 23.178 279.294 ↑ 1.0 181,766 1

Append (cost=0.00..8,268.45 rows=183,770 width=285) (actual time=0.025..279.294 rows=181,766 loops=1)

9. 191.513 191.513 ↑ 1.0 108,154 1

Seq Scan on lockkeyinfo lk (cost=0.00..5,600.42 rows=110,171 width=290) (actual time=0.024..191.513 rows=108,154 loops=1)

10. 64.603 64.603 ↓ 1.0 73,612 1

Seq Scan on keyhistoryinfo khi (cost=0.00..1,749.18 rows=73,599 width=278) (actual time=0.011..64.603 rows=73,612 loops=1)

11. 168.877 2,872.392 ↑ 3.0 122,902 1

Hash Right Join (cost=97,864.88..126,458.50 rows=374,484 width=1,285) (actual time=2,604.082..2,872.392 rows=122,902 loops=1)

  • Hash Cond: ((all_key.lockid)::text = (li.lockid)::text)
12. 72.517 2,551.482 ↑ 3.0 125,399 1

Unique (cost=94,669.42..106,867.71 rows=375,332 width=538) (actual time=2,451.977..2,551.482 rows=125,399 loops=1)

13. 376.142 2,478.965 ↑ 2.9 129,375 1

Sort (cost=94,669.42..95,607.75 rows=375,332 width=538) (actual time=2,451.975..2,478.965 rows=129,375 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: 37438kB
14. 14.264 2,102.823 ↑ 2.9 129,375 1

Append (cost=21,419.02..54,712.99 rows=375,332 width=538) (actual time=1,153.102..2,102.823 rows=129,375 loops=1)

15. 79.777 1,279.612 ↑ 3.0 123,817 1

Merge Join (cost=21,419.02..27,025.56 rows=373,465 width=538) (actual time=1,153.101..1,279.612 rows=123,817 loops=1)

  • Merge Cond: ((all_key.useraccount)::text = (_user_info.account)::text)
16. 181.912 778.186 ↓ 148.1 135,397 1

Sort (cost=6,706.61..6,708.90 rows=914 width=506) (actual time=751.574..778.186 rows=135,397 loops=1)

  • Sort Key: all_key.useraccount
  • Sort Method: quicksort Memory: 41279kB
17. 596.274 596.274 ↓ 148.1 135,398 1

CTE Scan on all_key (cost=0.00..6,661.66 rows=914 width=506) (actual time=0.034..596.274 rows=135,398 loops=1)

  • Filter: ((userrole <> 255) AND (keytype = ANY ('{0,2,1}'::integer[])) AND ((authtime)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 46368
18. 79.338 421.649 ↓ 1.7 137,420 1

Sort (cost=14,712.41..14,916.71 rows=81,721 width=44) (actual time=401.470..421.649 rows=137,420 loops=1)

  • Sort Key: _user_info.account
  • Sort Method: quicksort Memory: 9640kB
19. 11.860 342.311 ↓ 1.0 84,076 1

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

20. 102.628 330.451 ↓ 1.0 84,076 1

HashAggregate (cost=6,410.20..7,227.41 rows=81,721 width=210) (actual time=298.388..330.451 rows=84,076 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)
21. 118.350 227.823 ↓ 1.0 84,076 1

HashAggregate (cost=3,958.57..4,775.78 rows=81,721 width=210) (actual time=178.652..227.823 rows=84,076 loops=1)

  • Group Key: COALESCE(bk.certid, ac.userid, ac.account), bk.bluekeyaccount, ac.account, COALESCE(bk.certid, ac.userid)
22. 39.183 109.473 ↓ 1.0 84,076 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=69.632..109.473 rows=84,076 loops=1)

  • Hash Cond: ((bk.certid)::text = (ac.userid)::text)
  • Filter: (COALESCE(bk.certid, ac.userid, ac.account) IS NOT NULL)
23. 0.761 0.761 ↑ 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.761 rows=2,885 loops=1)

24. 31.671 69.529 ↑ 1.0 82,100 1

Hash (cost=1,373.32..1,373.32 rows=82,132 width=124) (actual time=69.528..69.529 rows=82,100 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 13197kB
25. 37.858 37.858 ↑ 1.0 82,100 1

Seq Scan on appuseraccount ac (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.034..37.858 rows=82,100 loops=1)

26. 31.394 808.947 ↓ 3.0 5,558 1

Merge Join (cost=21,419.02..22,057.45 rows=1,867 width=538) (actual time=746.653..808.947 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))
27. 182.590 347.699 ↓ 148.1 135,395 1

Sort (cost=6,706.61..6,708.90 rows=914 width=506) (actual time=317.801..347.699 rows=135,395 loops=1)

  • Sort Key: all_key_1.useraccount, all_key_1.lockid
  • Sort Method: quicksort Memory: 41279kB
28. 165.109 165.109 ↓ 148.1 135,398 1

CTE Scan on all_key all_key_1 (cost=0.00..6,661.66 rows=914 width=506) (actual time=0.017..165.109 rows=135,398 loops=1)

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

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

  • Sort Key: _user_info_1.bluekeyaccount, _user_info_1.lockid
  • Sort Method: quicksort Memory: 9688kB
30. 16.260 399.884 ↓ 1.0 84,076 1

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

31. 134.209 383.624 ↓ 1.0 84,076 1

HashAggregate (cost=6,410.20..7,227.41 rows=81,721 width=210) (actual time=340.951..383.624 rows=84,076 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)
32. 138.965 249.415 ↓ 1.0 84,076 1

HashAggregate (cost=3,958.57..4,775.78 rows=81,721 width=210) (actual time=185.451..249.415 rows=84,076 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)
33. 41.666 110.450 ↓ 1.0 84,076 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=68.265..110.450 rows=84,076 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)
34. 0.616 0.616 ↑ 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.616 rows=2,885 loops=1)

35. 30.796 68.168 ↑ 1.0 82,100 1

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

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

Seq Scan on appuseraccount ac_1 (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.021..37.372 rows=82,100 loops=1)

37. 25.384 152.033 ↑ 1.0 35,510 1

Hash (cost=2,731.51..2,731.51 rows=37,116 width=338) (actual time=152.033..152.033 rows=35,510 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 13229kB
38. 18.313 126.649 ↑ 1.0 35,510 1

Hash Left Join (cost=1,619.16..2,731.51 rows=37,116 width=338) (actual time=63.872..126.649 rows=35,510 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
39. 34.054 105.910 ↑ 1.0 35,510 1

Hash Join (cost=1,533.65..2,138.71 rows=37,116 width=233) (actual time=61.434..105.910 rows=35,510 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
40. 10.501 10.501 ↑ 1.0 37,178 1

Seq Scan on lockinfo li (cost=0.00..507.40 rows=37,200 width=70) (actual time=0.015..10.501 rows=37,178 loops=1)

  • Filter: (lockid IS NOT NULL)
41. 27.733 61.355 ↑ 1.0 53,672 1

Hash (cost=859.62..859.62 rows=53,922 width=227) (actual time=61.355..61.355 rows=53,672 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 13420kB
42. 33.622 33.622 ↑ 1.0 53,672 1

Seq Scan on houseinfo hi (cost=0.00..859.62 rows=53,922 width=227) (actual time=0.006..33.622 rows=53,672 loops=1)

43. 0.403 2.426 ↓ 1.0 1,149 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 179kB
44. 0.422 2.023 ↓ 1.0 1,149 1

Hash Right Join (cost=53.56..71.34 rows=1,134 width=117) (actual time=1.061..2.023 rows=1,149 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
45. 0.418 1.105 ↑ 1.0 1,092 1

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

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

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

47. 0.310 0.546 ↑ 1.0 1,100 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 145kB
48. 0.236 0.236 ↑ 1.0 1,100 1

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

49. 0.265 0.496 ↑ 1.0 1,134 1

Hash (cost=12.94..12.94 rows=1,134 width=60) (actual time=0.496..0.496 rows=1,134 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
50. 0.231 0.231 ↑ 1.0 1,134 1

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

Planning time : 2.742 ms
Execution time : 3,644.016 ms