explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B7Zb

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 47,169.403 ↑ 1.0 20 1

Limit (cost=2,321,304.83..2,321,304.88 rows=20 width=179) (actual time=47,169.370..47,169.403 rows=20 loops=1)

2. 87.011 47,169.371 ↑ 5,269.5 20 1

Sort (cost=2,321,304.83..2,321,568.30 rows=105,390 width=179) (actual time=47,169.369..47,169.371 rows=20 loops=1)

  • Sort Key: houseinfo.xzqhdm, ((replace((houseinfo.detailaddr)::text, '浙江省湖州市长兴县'::text, ''::text) || (houseinfo.housenumber)::text))
  • Sort Method: top-N heapsort Memory: 36kB
3. 18.982 47,082.360 ↑ 1.0 102,670 1

Append (cost=19.52..2,318,500.44 rows=105,390 width=179) (actual time=0.999..47,082.360 rows=102,670 loops=1)

4. 176.458 7,949.763 ↑ 1.0 100,036 1

Nested Loop (cost=19.52..1,540,531.17 rows=103,858 width=178) (actual time=0.998..7,949.763 rows=100,036 loops=1)

5. 27.003 2,171.289 ↑ 1.0 50,018 1

Merge Left Join (cost=8.68..425,401.82 rows=51,929 width=194) (actual time=0.336..2,171.289 rows=50,018 loops=1)

  • Merge Cond: ((houseinfo.houseid)::text = _regulated_house_alpub.houseid)
6. 28.269 2,144.095 ↑ 1.0 50,018 1

Nested Loop (cost=8.54..425,268.51 rows=51,929 width=186) (actual time=0.320..2,144.095 rows=50,018 loops=1)

7. 115.106 115.106 ↑ 1.0 50,018 1

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..1,926.92 rows=51,929 width=146) (actual time=0.024..115.106 rows=50,018 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 27805
8. 850.306 2,000.720 ↑ 1.0 1 50,018

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual time=0.040..0.040 rows=1 loops=50,018)

9. 1,150.414 1,150.414 ↑ 3.8 13 50,018

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.004..0.023 rows=13 loops=50,018)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
10. 0.191 0.191 ↑ 1.0 122 1

Index Scan using _regulated_house_alpub_pkey on _regulated_house_alpub (cost=0.14..2.37 rows=122 width=41) (actual time=0.014..0.191 rows=122 loops=1)

11. 50.018 5,602.016 ↑ 1.0 2 50,018

Append (cost=10.83..21.44 rows=2 width=32) (actual time=0.097..0.112 rows=2 loops=50,018)

12. 746.760 4,851.746 ↑ 1.0 1 50,018

Aggregate (cost=10.83..10.84 rows=1 width=32) (actual time=0.097..0.097 rows=1 loops=50,018)

13. 60.342 850.306 ↑ 2.0 1 50,018

Nested Loop (cost=0.83..2.19 rows=2 width=39) (actual time=0.010..0.017 rows=1 loops=50,018)

14. 400.144 400.144 ↑ 2.0 1 50,018

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=24) (actual time=0.005..0.008 rows=1 loops=50,018)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
15. 389.820 389.820 ↑ 1.0 1 64,970

Index Scan using appuseraccount_account_idx on appuseraccount (cost=0.42..0.64 rows=1 width=39) (actual time=0.006..0.006 rows=1 loops=64,970)

  • Index Cond: ((account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
16.          

SubPlan (for Aggregate)

17. 250.360 1,752.520 ↑ 1.0 1 62,590

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.028..0.028 rows=1 loops=62,590)

18. 563.310 1,502.160 ↓ 9.0 9 62,590

Bitmap Heap Scan on opendoorrecord opendoorrecord_2 (cost=2.03..2.15 rows=1 width=20) (actual time=0.017..0.024 rows=9 loops=62,590)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
  • Heap Blocks: exact=550537
19. 154.552 938.850 ↓ 0.0 0 62,590

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=62,590)

20. 500.720 500.720 ↑ 2.0 24 62,590

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.008..0.008 rows=24 loops=62,590)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
21. 283.578 283.578 ↑ 1.6 31 47,263

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.006..0.006 rows=31 loops=47,263)

  • Index Cond: ((houseid)::text = ($0)::text)
22. 250.360 1,502.160 ↑ 1.0 1 62,590

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=62,590)

23. 500.720 1,251.800 ↓ 9.0 9 62,590

Bitmap Heap Scan on opendoorrecord opendoorrecord_3 (cost=2.03..2.15 rows=1 width=20) (actual time=0.013..0.020 rows=9 loops=62,590)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
  • Heap Blocks: exact=550537
24. 154.552 751.080 ↓ 0.0 0 62,590

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=62,590)

25. 312.950 312.950 ↑ 2.0 24 62,590

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.005..0.005 rows=24 loops=62,590)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
26. 283.578 283.578 ↑ 1.6 31 47,263

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.006..0.006 rows=31 loops=47,263)

  • Index Cond: ((houseid)::text = ($0)::text)
27. 145.787 700.252 ↑ 1.0 1 50,018

Aggregate (cost=10.56..10.57 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=50,018)

28. 70.132 500.180 ↓ 0.0 0 50,018

Nested Loop (cost=0.70..1.91 rows=2 width=70) (actual time=0.010..0.010 rows=0 loops=50,018)

29. 300.108 300.108 ↑ 2.0 1 50,018

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo lockkeyinfo_1 (cost=0.42..0.91 rows=2 width=24) (actual time=0.005..0.006 rows=1 loops=50,018)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
30. 129.940 129.940 ↓ 0.0 0 64,970

Index Scan using "pk_bluekeyuse_1570385661_103 " on bluekeyuserinfo (cost=0.28..0.50 rows=1 width=70) (actual time=0.002..0.002 rows=0 loops=64,970)

  • Index Cond: ((bluekeyaccount)::text = (COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text)
31.          

SubPlan (for Aggregate)

32. 3.102 31.020 ↑ 1.0 1 1,551

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=1,551)

33. 4.653 27.918 ↓ 3.0 3 1,551

Bitmap Heap Scan on opendoorrecord opendoorrecord_4 (cost=2.03..2.15 rows=1 width=20) (actual time=0.017..0.018 rows=3 loops=1,551)

  • Recheck Cond: (((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text) AND ((houseid)::text = ($5)::text))
  • Heap Blocks: exact=2095
34. 2.280 23.265 ↓ 0.0 0 1,551

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1,551)

35. 15.510 15.510 ↑ 1.8 26 1,551

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.010..0.010 rows=26 loops=1,551)

  • Index Cond: ((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text)
36. 5.475 5.475 ↑ 2.2 22 1,095

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.005..0.005 rows=22 loops=1,095)

  • Index Cond: ((houseid)::text = ($5)::text)
37. 3.102 23.265 ↑ 1.0 1 1,551

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1,551)

38. 3.102 20.163 ↓ 3.0 3 1,551

Bitmap Heap Scan on opendoorrecord opendoorrecord_5 (cost=2.03..2.15 rows=1 width=20) (actual time=0.012..0.013 rows=3 loops=1,551)

  • Recheck Cond: (((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text) AND ((houseid)::text = ($5)::text))
  • Heap Blocks: exact=2095
39. 2.280 17.061 ↓ 0.0 0 1,551

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1,551)

40. 9.306 9.306 ↑ 1.8 26 1,551

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.006..0.006 rows=26 loops=1,551)

  • Index Cond: ((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text)
41. 5.475 5.475 ↑ 2.2 22 1,095

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.005..0.005 rows=22 loops=1,095)

  • Index Cond: ((houseid)::text = ($5)::text)
42. 11.047 39,113.615 ↓ 1.7 2,634 1

Nested Loop (cost=664.56..776,388.42 rows=1,532 width=267) (actual time=18.956..39,113.615 rows=2,634 loops=1)

43. 3.471 693.580 ↓ 1.7 1,317 1

Nested Loop Left Join (cost=187.44..43,639.51 rows=766 width=184) (actual time=4.453..693.580 rows=1,317 loops=1)

44. 5.104 686.158 ↓ 1.7 1,317 1

Nested Loop (cost=187.29..43,508.45 rows=766 width=176) (actual time=4.441..686.158 rows=1,317 loops=1)

45. 2.535 13.335 ↓ 1.7 1,317 1

Hash Join (cost=130.78..205.53 rows=766 width=155) (actual time=3.301..13.335 rows=1,317 loops=1)

  • Hash Cond: ((houseinfo_1.customid)::text = (custominfo.customid)::text)
46. 5.794 10.690 ↓ 1.6 1,327 1

Hash Join (cost=127.34..199.79 rows=848 width=159) (actual time=3.183..10.690 rows=1,327 loops=1)

  • Hash Cond: ((lockinfo.houseid)::text = (houseinfo_1.houseid)::text)
47. 1.729 1.729 ↑ 1.0 4,516 1

Seq Scan on lockinfo (cost=0.00..60.56 rows=4,526 width=43) (actual time=0.005..1.729 rows=4,516 loops=1)

48. 0.686 3.167 ↓ 1.5 1,599 1

Hash (cost=113.62..113.62 rows=1,097 width=140) (actual time=3.167..3.167 rows=1,599 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 303kB
49. 2.481 2.481 ↓ 1.5 1,599 1

Seq Scan on houseinfo houseinfo_1 (cost=0.00..113.62 rows=1,097 width=140) (actual time=0.008..2.481 rows=1,599 loops=1)

  • Filter: (((xzqhdm)::text ~~ '330522%'::text) AND ((housecategory)::text = '2'::text))
  • Rows Removed by Filter: 4241
50. 0.042 0.110 ↓ 1.2 155 1

Hash (cost=1.81..1.81 rows=131 width=61) (actual time=0.110..0.110 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
51. 0.068 0.068 ↓ 1.2 155 1

Seq Scan on custominfo (cost=0.00..1.81 rows=131 width=61) (actual time=0.018..0.068 rows=155 loops=1)

52. 301.593 667.719 ↑ 1.0 1 1,317

Aggregate (cost=56.50..56.51 rows=1 width=40) (actual time=0.507..0.507 rows=1 loops=1,317)

53. 366.126 366.126 ↑ 1.7 208 1,317

Index Only Scan using opendoorrecord_lockid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.42..42.89 rows=363 width=20) (actual time=0.020..0.278 rows=208 loops=1,317)

  • Index Cond: (lockid = (lockinfo.lockid)::text)
  • Heap Fetches: 274401
54. 3.951 3.951 ↓ 0.0 0 1,317

Index Scan using _regulated_house_alemp_pkey on _regulated_house_alemp (cost=0.15..0.17 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=1,317)

  • Index Cond: ((houseinfo_1.houseid)::text = houseid)
55. 9.219 38,408.988 ↑ 1.0 2 1,317

Append (cost=477.12..956.56 rows=2 width=32) (actual time=14.601..29.164 rows=2 loops=1,317)

56. 64.588 19,225.566 ↑ 1.0 1 1,317

Aggregate (cost=477.12..477.13 rows=1 width=32) (actual time=14.598..14.598 rows=1 loops=1,317)

57. 17.531 19,060.941 ↑ 1.0 2 1,317

Nested Loop (cost=0.80..473.99 rows=2 width=59) (actual time=7.653..14.473 rows=2 loops=1,317)

58. 1,637.031 19,020.114 ↓ 1.5 3 1,317

Hash Join (cost=0.51..473.37 rows=2 width=44) (actual time=7.622..14.442 rows=3 loops=1,317)

  • Hash Cond: ((lockkeyinfo_2.lockid)::text = (lockinfo_1.lockid)::text)
59. 17,369.913 17,369.913 ↓ 1.1 8,216 1,317

Seq Scan on lockkeyinfo lockkeyinfo_2 (cost=0.00..453.05 rows=7,536 width=44) (actual time=0.005..13.189 rows=8,216 loops=1,317)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 6820
60. 3.951 13.170 ↑ 1.0 1 1,317

Hash (cost=0.50..0.50 rows=1 width=19) (actual time=0.010..0.010 rows=1 loops=1,317)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 9.219 9.219 ↑ 1.0 1 1,317

Index Scan using idx_index_hous_lockinfo_1575546514_2 on lockinfo lockinfo_1 (cost=0.28..0.50 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1,317)

  • Index Cond: ((houseid)::text = (houseinfo_1.houseid)::text)
62. 23.296 23.296 ↑ 1.0 1 3,328

Index Scan using "pk_appuseracc_1570384907_16 " on appuseraccount appuseraccount_1 (cost=0.29..0.31 rows=1 width=39) (actual time=0.007..0.007 rows=1 loops=3,328)

  • Index Cond: ((account)::text = (COALESCE(lockkeyinfo_2.keyholder, lockkeyinfo_2.useraccount))::text)
63.          

SubPlan (for Aggregate)

64. 6.454 64.540 ↑ 1.0 1 3,227

Result (cost=0.77..0.78 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=3,227)

65.          

Initplan (for Result)

66. 3.227 58.086 ↑ 1.0 1 3,227

Limit (cost=0.55..0.77 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=3,227)

67. 54.859 54.859 ↑ 1.0 1 3,227

Index Only Scan Backward using idx_idx_userid_opendoorre_1578488409_1 on opendoorrecord opendoorrecord_6 (cost=0.55..0.77 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=3,227)

  • Index Cond: ((userid = (appuseraccount_1.account)::text) AND (lockid = (lockkeyinfo_2.lockid)::text) AND (opentime IS NOT NULL))
  • Heap Fetches: 3082
68. 3.227 35.497 ↑ 1.0 1 3,227

Result (cost=0.77..0.78 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=3,227)

69.          

Initplan (for Result)

70. 3.227 32.270 ↑ 1.0 1 3,227

Limit (cost=0.55..0.77 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=3,227)

71. 29.043 29.043 ↑ 1.0 1 3,227

Index Only Scan Backward using idx_idx_userid_opendoorre_1578488409_1 on opendoorrecord opendoorrecord_7 (cost=0.55..0.77 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=3,227)

  • Index Cond: ((userid = (appuseraccount_1.account)::text) AND (lockid = (lockkeyinfo_2.lockid)::text) AND (opentime IS NOT NULL))
  • Heap Fetches: 3082
72. 19.587 19,174.203 ↑ 1.0 1 1,317

Aggregate (cost=479.38..479.39 rows=1 width=32) (actual time=14.559..14.559 rows=1 loops=1,317)

73. 18.438 19,153.131 ↓ 0.0 0 1,317

Nested Loop (cost=0.51..477.81 rows=1 width=178) (actual time=14.542..14.543 rows=0 loops=1,317)

  • Join Filter: ((COALESCE(lockkeyinfo_3.keyholder, lockkeyinfo_3.useraccount))::text = (bluekeyuserinfo_1.bluekeyaccount)::text)
  • Rows Removed by Join Filter: 235
74. 27.657 27.657 ↑ 1.1 93 1,317

Seq Scan on bluekeyuserinfo bluekeyuserinfo_1 (cost=0.00..1.41 rows=101 width=158) (actual time=0.003..0.021 rows=93 loops=1,317)

75. 94.824 19,107.036 ↓ 1.5 3 122,481

Materialize (cost=0.51..473.38 rows=2 width=44) (actual time=0.082..0.156 rows=3 loops=122,481)

76. 1,634.397 19,012.212 ↓ 1.5 3 1,317

Hash Join (cost=0.51..473.37 rows=2 width=44) (actual time=7.617..14.436 rows=3 loops=1,317)

  • Hash Cond: ((lockkeyinfo_3.lockid)::text = (lockinfo_2.lockid)::text)
77. 17,362.011 17,362.011 ↓ 1.1 8,216 1,317

Seq Scan on lockkeyinfo lockkeyinfo_3 (cost=0.00..453.05 rows=7,536 width=44) (actual time=0.004..13.183 rows=8,216 loops=1,317)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 6820
78. 2.634 15.804 ↑ 1.0 1 1,317

Hash (cost=0.50..0.50 rows=1 width=19) (actual time=0.012..0.012 rows=1 loops=1,317)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 13.170 13.170 ↑ 1.0 1 1,317

Index Scan using idx_index_hous_lockinfo_1575546514_2 on lockinfo lockinfo_2 (cost=0.28..0.50 rows=1 width=19) (actual time=0.009..0.010 rows=1 loops=1,317)

  • Index Cond: ((houseid)::text = (houseinfo_1.houseid)::text)
80.          

SubPlan (for Aggregate)

81. 0.066 0.990 ↑ 1.0 1 33

Result (cost=0.77..0.78 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=33)

82.          

Initplan (for Result)

83. 0.099 0.924 ↑ 1.0 1 33

Limit (cost=0.55..0.77 rows=1 width=32) (actual time=0.027..0.028 rows=1 loops=33)

84. 0.825 0.825 ↑ 1.0 1 33

Index Only Scan Backward using idx_idx_userid_opendoorre_1578488409_1 on opendoorrecord opendoorrecord_8 (cost=0.55..0.77 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=33)

  • Index Cond: ((userid = (bluekeyuserinfo_1.bluekeyaccount)::text) AND (lockid = (lockkeyinfo_3.lockid)::text) AND (opentime IS NOT NULL))
  • Heap Fetches: 31
85. 0.033 0.495 ↑ 1.0 1 33

Result (cost=0.77..0.78 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=33)

86.          

Initplan (for Result)

87. 0.066 0.462 ↑ 1.0 1 33

Limit (cost=0.55..0.77 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=33)

88. 0.396 0.396 ↑ 1.0 1 33

Index Only Scan Backward using idx_idx_userid_opendoorre_1578488409_1 on opendoorrecord opendoorrecord_9 (cost=0.55..0.77 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=33)

  • Index Cond: ((userid = (bluekeyuserinfo_1.bluekeyaccount)::text) AND (lockid = (lockkeyinfo_3.lockid)::text) AND (opentime IS NOT NULL))
  • Heap Fetches: 31
Planning time : 4.133 ms
Execution time : 47,169.991 ms