explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p34m

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 8,562.437 ↑ 1.0 20 1

Limit (cost=1,595,364.28..1,595,364.33 rows=20 width=179) (actual time=8,562.395..8,562.437 rows=20 loops=1)

2. 82.108 8,562.395 ↑ 5,269.5 20 1

Sort (cost=1,595,364.28..1,595,627.76 rows=105,390 width=179) (actual time=8,562.393..8,562.395 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. 17.457 8,480.287 ↑ 1.0 102,670 1

Append (cost=19.52..1,592,559.89 rows=105,390 width=179) (actual time=1.173..8,480.287 rows=102,670 loops=1)

4. 162.752 7,664.842 ↑ 1.0 100,036 1

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

5. 25.803 2,050.128 ↑ 1.0 50,018 1

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

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

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

7. 107.993 107.993 ↑ 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.015..107.993 rows=50,018 loops=1)

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

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

9. 1,050.378 1,050.378 ↑ 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.021 rows=13 loops=50,018)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
10. 0.184 0.184 ↑ 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.009..0.184 rows=122 loops=1)

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

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

12. 771.904 4,701.692 ↑ 1.0 1 50,018

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

13. 125.312 800.288 ↑ 2.0 1 50,018

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

14. 350.126 350.126 ↑ 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.007 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. 324.850 324.850 ↑ 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.005..0.005 rows=1 loops=64,970)

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

SubPlan (for Aggregate)

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

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

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

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

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

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.014..0.014 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. 187.770 1,439.570 ↑ 1.0 1 62,590

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

23. 563.310 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=550548
24. 91.962 688.490 ↓ 0.0 0 62,590

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.011..0.011 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. 147.338 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 29.469 ↑ 1.0 1 1,551

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

33. 4.653 26.367 ↓ 3.0 3 1,551

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

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

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

35. 13.959 13.959 ↑ 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.009..0.009 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. 4.459 797.988 ↓ 1.7 2,634 1

Nested Loop (cost=192.77..50,447.87 rows=1,532 width=267) (actual time=4.211..797.988 rows=2,634 loops=1)

43. 1.072 613.100 ↓ 1.7 1,317 1

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

44. 1.563 609.394 ↓ 1.7 1,317 1

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

45. 1.066 8.596 ↓ 1.7 1,317 1

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

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

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

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

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

48. 0.626 3.050 ↓ 1.5 1,599 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 303kB
49. 2.424 2.424 ↓ 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.424 rows=1,599 loops=1)

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

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

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

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

52. 293.691 599.235 ↑ 1.0 1 1,317

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

53. 305.544 305.544 ↑ 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.009..0.232 rows=208 loops=1,317)

  • Index Cond: (lockid = (lockinfo.lockid)::text)
  • Heap Fetches: 274401
54. 2.634 2.634 ↓ 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.002..0.002 rows=0 loops=1,317)

  • Index Cond: ((houseinfo_1.houseid)::text = houseid)
55. 2.634 180.429 ↑ 1.0 2 1,317

Append (cost=5.34..8.85 rows=2 width=32) (actual time=0.114..0.137 rows=2 loops=1,317)

56. 35.090 148.821 ↑ 1.0 1 1,317

Aggregate (cost=5.34..5.35 rows=1 width=32) (actual time=0.113..0.113 rows=1 loops=1,317)

57. 5.126 39.510 ↑ 1.0 2 1,317

Nested Loop (cost=0.85..2.20 rows=2 width=59) (actual time=0.016..0.030 rows=2 loops=1,317)

58. 2.634 21.072 ↓ 1.5 3 1,317

Nested Loop (cost=0.57..1.58 rows=2 width=44) (actual time=0.011..0.016 rows=3 loops=1,317)

59. 5.268 5.268 ↑ 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.004..0.004 rows=1 loops=1,317)

  • Index Cond: ((houseid)::text = (houseinfo_1.houseid)::text)
60. 13.170 13.170 ↑ 1.0 3 1,317

Index Scan using lockkeyinfo_lockid_idx on lockkeyinfo lockkeyinfo_2 (cost=0.29..1.05 rows=3 width=44) (actual time=0.005..0.010 rows=3 loops=1,317)

  • Index Cond: ((lockid)::text = (lockinfo_1.lockid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
61. 13.312 13.312 ↑ 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.004..0.004 rows=1 loops=3,328)

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

SubPlan (for Aggregate)

63. 3.227 41.951 ↑ 1.0 1 3,227

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

64.          

Initplan (for Result)

65. 3.227 38.724 ↑ 1.0 1 3,227

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

66. 35.497 35.497 ↑ 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.011..0.011 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
67. 3.227 32.270 ↑ 1.0 1 3,227

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

68.          

Initplan (for Result)

69. 3.227 29.043 ↑ 1.0 1 3,227

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

70. 25.816 25.816 ↑ 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.008..0.008 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
71. 4.245 28.974 ↑ 1.0 1 1,317

Aggregate (cost=3.47..3.48 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=1,317)

72. 0.000 23.706 ↓ 0.0 0 1,317

Nested Loop (cost=0.71..1.90 rows=1 width=178) (actual time=0.018..0.018 rows=0 loops=1,317)

73. 3.951 17.121 ↓ 1.5 3 1,317

Nested Loop (cost=0.57..1.58 rows=2 width=44) (actual time=0.009..0.013 rows=3 loops=1,317)

74. 3.951 3.951 ↑ 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.003..0.003 rows=1 loops=1,317)

  • Index Cond: ((houseid)::text = (houseinfo_1.houseid)::text)
75. 9.219 9.219 ↑ 1.0 3 1,317

Index Scan using lockkeyinfo_lockid_idx on lockkeyinfo lockkeyinfo_3 (cost=0.29..1.05 rows=3 width=44) (actual time=0.004..0.007 rows=3 loops=1,317)

  • Index Cond: ((lockid)::text = (lockinfo_2.lockid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
76. 6.656 6.656 ↓ 0.0 0 3,328

Index Scan using "pk_bluekeyuse_1570384910_19 " on bluekeyuserinfo bluekeyuserinfo_1 (cost=0.14..0.16 rows=1 width=158) (actual time=0.002..0.002 rows=0 loops=3,328)

  • Index Cond: ((bluekeyaccount)::text = (COALESCE(lockkeyinfo_3.keyholder, lockkeyinfo_3.useraccount))::text)
77.          

SubPlan (for Aggregate)

78. 0.033 0.594 ↑ 1.0 1 33

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

79.          

Initplan (for Result)

80. 0.066 0.561 ↑ 1.0 1 33

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

81. 0.495 0.495 ↑ 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.015..0.015 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
82. 0.033 0.429 ↑ 1.0 1 33

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

83.          

Initplan (for Result)

84. 0.066 0.396 ↑ 1.0 1 33

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

85. 0.330 0.330 ↑ 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.010..0.010 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 : 5.210 ms
Execution time : 8,563.194 ms