explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sm7O

Settings
# exclusive inclusive rows x rows loops node
1. 135.016 5,282.225 ↑ 11.2 33,300 1

Sort (cost=272,571.70..273,503.23 rows=372,612 width=369) (actual time=5,251.894..5,282.225 rows=33,300 loops=1)

  • Sort Key: a.lock_createtime DESC
  • Sort Method: external merge Disk: 11232kB
2. 18.635 5,147.209 ↑ 11.2 33,300 1

Subquery Scan on a (cost=209,301.35..234,452.66 rows=372,612 width=369) (actual time=4,769.669..5,147.209 rows=33,300 loops=1)

3. 231.782 5,128.574 ↑ 11.2 33,300 1

GroupAggregate (cost=209,301.35..229,795.01 rows=372,612 width=337) (actual time=4,769.666..5,128.574 rows=33,300 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. 967.474 4,896.792 ↑ 3.5 105,467 1

Sort (cost=209,301.35..210,232.88 rows=372,612 width=459) (actual time=4,769.643..4,896.792 rows=105,467 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: external merge Disk: 36952kB
5. 38.175 3,929.318 ↑ 3.5 105,467 1

Subquery Scan on hk (cost=134,317.13..170,381.72 rows=372,612 width=459) (actual time=3,321.507..3,929.318 rows=105,467 loops=1)

6. 70.246 3,891.143 ↑ 3.5 105,467 1

Subquery Scan on a_1 (cost=134,317.13..166,655.60 rows=372,612 width=1,317) (actual time=3,321.506..3,891.143 rows=105,467 loops=1)

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

CTE all_key

8. 21.646 282.388 ↑ 1.0 181,763 1

Append (cost=0.00..8,268.45 rows=183,770 width=285) (actual time=0.024..282.388 rows=181,763 loops=1)

9. 198.181 198.181 ↑ 1.0 108,151 1

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

10. 62.561 62.561 ↓ 1.0 73,612 1

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

11. 292.576 3,820.897 ↑ 3.0 122,899 1

Hash Right Join (cost=126,048.68..154,642.30 rows=374,484 width=1,285) (actual time=3,321.503..3,820.897 rows=122,899 loops=1)

  • Hash Cond: ((all_key.lockid)::text = (li.lockid)::text)
12. 118.362 3,276.366 ↑ 3.0 125,397 1

Unique (cost=122,854.16..135,052.45 rows=375,332 width=538) (actual time=3,069.236..3,276.366 rows=125,397 loops=1)

13. 360.206 3,158.004 ↑ 2.9 129,373 1

Sort (cost=122,854.16..123,792.49 rows=375,332 width=538) (actual time=3,069.234..3,158.004 rows=129,373 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: external merge Disk: 24008kB
14. 14.007 2,797.798 ↑ 2.9 129,373 1

Append (cost=35,511.39..82,897.74 rows=375,332 width=538) (actual time=1,335.637..2,797.798 rows=129,373 loops=1)

15. 77.276 1,471.879 ↑ 3.0 123,815 1

Merge Join (cost=35,511.39..41,117.93 rows=373,465 width=538) (actual time=1,335.635..1,471.879 rows=123,815 loops=1)

  • Merge Cond: ((all_key.useraccount)::text = (_user_info.account)::text)
16. 228.256 845.826 ↓ 148.1 135,396 1

Sort (cost=6,706.61..6,708.90 rows=914 width=506) (actual time=805.532..845.826 rows=135,396 loops=1)

  • Sort Key: all_key.useraccount
  • Sort Method: external merge Disk: 22696kB
17. 617.570 617.570 ↓ 148.1 135,397 1

CTE Scan on all_key (cost=0.00..6,661.66 rows=914 width=506) (actual time=0.033..617.570 rows=135,397 loops=1)

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

Sort (cost=28,804.78..29,009.08 rows=81,721 width=44) (actual time=530.040..548.777 rows=137,416 loops=1)

  • Sort Key: _user_info.account
  • Sort Method: quicksort Memory: 9640kB
19. 13.792 475.087 ↓ 1.0 84,072 1

Subquery Scan on _user_info (cost=19,481.06..22,136.99 rows=81,721 width=44) (actual time=410.462..475.087 rows=84,072 loops=1)

20. 32.382 461.295 ↓ 1.0 84,072 1

Unique (cost=19,481.06..21,319.78 rows=81,721 width=210) (actual time=410.458..461.295 rows=84,072 loops=1)

21. 101.828 428.913 ↓ 1.0 84,072 1

Sort (cost=19,481.06..19,685.36 rows=81,721 width=210) (actual time=410.456..428.913 rows=84,072 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: 12344kB
22. 69.883 327.085 ↓ 1.0 84,072 1

GroupAggregate (cost=9,882.64..12,334.27 rows=81,721 width=210) (actual time=235.799..327.085 rows=84,072 loops=1)

  • Group Key: (COALESCE(bk.certid, ac.userid, ac.account)), bk.bluekeyaccount, ac.account, (COALESCE(bk.certid, ac.userid))
23. 144.971 257.202 ↓ 1.0 84,072 1

Sort (cost=9,882.64..10,086.94 rows=81,721 width=320) (actual time=235.782..257.202 rows=84,072 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: 13128kB
24. 37.736 112.231 ↓ 1.0 84,072 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=73.813..112.231 rows=84,072 loops=1)

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

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

26. 35.741 73.693 ↑ 1.0 82,096 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 13196kB
27. 37.952 37.952 ↑ 1.0 82,096 1

Seq Scan on appuseraccount ac (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.046..37.952 rows=82,096 loops=1)

28. 37.000 1,311.912 ↓ 3.0 5,558 1

Merge Join (cost=35,511.39..36,149.82 rows=1,867 width=538) (actual time=1,195.009..1,311.912 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))
29. 281.880 494.050 ↓ 148.1 135,394 1

Sort (cost=6,706.61..6,708.90 rows=914 width=506) (actual time=415.157..494.050 rows=135,394 loops=1)

  • Sort Key: all_key_1.useraccount, all_key_1.lockid
  • Sort Method: external merge Disk: 22696kB
30. 212.170 212.170 ↓ 148.1 135,397 1

CTE Scan on all_key all_key_1 (cost=0.00..6,661.66 rows=914 width=506) (actual time=0.059..212.170 rows=135,397 loops=1)

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

Sort (cost=28,804.78..29,009.08 rows=81,721 width=70) (actual time=779.843..780.862 rows=5,586 loops=1)

  • Sort Key: _user_info_1.bluekeyaccount, _user_info_1.lockid
  • Sort Method: quicksort Memory: 9688kB
32. 37.964 728.689 ↓ 1.0 84,072 1

Subquery Scan on _user_info_1 (cost=19,481.06..22,136.99 rows=81,721 width=70) (actual time=585.499..728.689 rows=84,072 loops=1)

33. 63.824 690.725 ↓ 1.0 84,072 1

Unique (cost=19,481.06..21,319.78 rows=81,721 width=210) (actual time=585.495..690.725 rows=84,072 loops=1)

34. 172.354 626.901 ↓ 1.0 84,072 1

Sort (cost=19,481.06..19,685.36 rows=81,721 width=210) (actual time=585.493..626.901 rows=84,072 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: 12344kB
35. 112.437 454.547 ↓ 1.0 84,072 1

GroupAggregate (cost=9,882.64..12,334.27 rows=81,721 width=210) (actual time=310.421..454.547 rows=84,072 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))
36. 199.900 342.110 ↓ 1.0 84,072 1

Sort (cost=9,882.64..10,086.94 rows=81,721 width=320) (actual time=310.408..342.110 rows=84,072 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: 13128kB
37. 49.038 142.210 ↓ 1.0 84,072 1

Hash Full Join (cost=2,399.97..2,528.45 rows=81,721 width=320) (actual time=92.567..142.210 rows=84,072 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)
38. 0.731 0.731 ↑ 1.0 2,885 1

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

39. 42.628 92.441 ↑ 1.0 82,096 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 13196kB
40. 49.813 49.813 ↑ 1.0 82,096 1

Seq Scan on appuseraccount ac_1 (cost=0.00..1,373.32 rows=82,132 width=124) (actual time=0.022..49.813 rows=82,096 loops=1)

41. 44.204 251.955 ↑ 1.0 35,508 1

Hash (cost=2,730.56..2,730.56 rows=37,116 width=338) (actual time=251.954..251.955 rows=35,508 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 13228kB
42. 32.171 207.751 ↑ 1.0 35,508 1

Hash Left Join (cost=1,618.22..2,730.56 rows=37,116 width=338) (actual time=108.720..207.751 rows=35,508 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
43. 52.723 171.847 ↑ 1.0 35,508 1

Hash Join (cost=1,532.70..2,137.76 rows=37,116 width=233) (actual time=104.964..171.847 rows=35,508 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
44. 14.433 14.433 ↑ 1.0 37,176 1

Seq Scan on lockinfo li (cost=0.00..507.40 rows=37,200 width=70) (actual time=0.019..14.433 rows=37,176 loops=1)

  • Filter: (lockid IS NOT NULL)
45. 48.236 104.691 ↑ 1.0 53,672 1

Hash (cost=859.09..859.09 rows=53,889 width=227) (actual time=104.691..104.691 rows=53,672 loops=1)

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

Seq Scan on houseinfo hi (cost=0.00..859.09 rows=53,889 width=227) (actual time=0.004..56.455 rows=53,672 loops=1)

47. 0.637 3.733 ↓ 1.0 1,149 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 179kB
48. 0.677 3.096 ↓ 1.0 1,149 1

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

  • Hash Cond: (ys."行政区划_id" = xz.id)
49. 0.621 1.677 ↑ 1.0 1,092 1

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

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

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

51. 0.487 0.821 ↑ 1.0 1,100 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 145kB
52. 0.334 0.334 ↑ 1.0 1,100 1

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

53. 0.421 0.742 ↑ 1.0 1,134 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
54. 0.321 0.321 ↑ 1.0 1,134 1

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

Planning time : 3.775 ms
Execution time : 5,384.787 ms