explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qsQu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=2,321,304.83..2,321,304.88 rows=20 width=179) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=2,321,304.83..2,321,568.30 rows=105,390 width=179) (actual rows= loops=)

  • Sort Key: houseinfo.xzqhdm, ((replace((houseinfo.detailaddr)::text, '浙江省湖州市长兴县'::text, ''::text) || (houseinfo.housenumber)::text))
3. 0.000 0.000 ↓ 0.0

Append (cost=19.52..2,318,500.44 rows=105,390 width=179) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=19.52..1,540,531.17 rows=103,858 width=178) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8.68..425,401.82 rows=51,929 width=194) (actual rows= loops=)

  • Merge Cond: ((houseinfo.houseid)::text = _regulated_house_alpub.houseid)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.54..425,268.51 rows=51,929 width=186) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

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

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
8. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

Append (cost=10.83..21.44 rows=2 width=32) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Aggregate (cost=10.83..10.84 rows=1 width=32) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..2.19 rows=2 width=39) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=24) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
15. 0.000 0.000 ↓ 0.0

Index Scan using appuseraccount_account_idx on appuseraccount (cost=0.42..0.64 rows=1 width=39) (actual rows= loops=)

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

SubPlan (for Aggregate)

17. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on opendoorrecord opendoorrecord_2 (cost=2.03..2.15 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
19. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual rows= loops=)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
21. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual rows= loops=)

  • Index Cond: ((houseid)::text = ($0)::text)
22. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on opendoorrecord opendoorrecord_3 (cost=2.03..2.15 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
24. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual rows= loops=)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
26. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual rows= loops=)

  • Index Cond: ((houseid)::text = ($0)::text)
27. 0.000 0.000 ↓ 0.0

Aggregate (cost=10.56..10.57 rows=1 width=32) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..1.91 rows=2 width=70) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo lockkeyinfo_1 (cost=0.42..0.91 rows=2 width=24) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
30. 0.000 0.000 ↓ 0.0

Index Scan using "pk_bluekeyuse_1570385661_103 " on bluekeyuserinfo (cost=0.28..0.50 rows=1 width=70) (actual rows= loops=)

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

SubPlan (for Aggregate)

32. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on opendoorrecord opendoorrecord_4 (cost=2.03..2.15 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text) AND ((houseid)::text = ($5)::text))
34. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual rows= loops=)

  • Index Cond: ((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text)
36. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual rows= loops=)

  • Index Cond: ((houseid)::text = ($5)::text)
37. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on opendoorrecord opendoorrecord_5 (cost=2.03..2.15 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text) AND ((houseid)::text = ($5)::text))
39. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual rows= loops=)

  • Index Cond: ((userid)::text = (bluekeyuserinfo.bluekeyaccount)::text)
41. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual rows= loops=)

  • Index Cond: ((houseid)::text = ($5)::text)
42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=664.56..776,388.42 rows=1,532 width=267) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=187.44..43,639.51 rows=766 width=184) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=187.29..43,508.45 rows=766 width=176) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash Join (cost=130.78..205.53 rows=766 width=155) (actual rows= loops=)

  • Hash Cond: ((houseinfo_1.customid)::text = (custominfo.customid)::text)
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=127.34..199.79 rows=848 width=159) (actual rows= loops=)

  • Hash Cond: ((lockinfo.houseid)::text = (houseinfo_1.houseid)::text)
47. 0.000 0.000 ↓ 0.0

Seq Scan on lockinfo (cost=0.00..60.56 rows=4,526 width=43) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

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

  • Filter: (((xzqhdm)::text ~~ '330522%'::text) AND ((housecategory)::text = '2'::text))
50. 0.000 0.000 ↓ 0.0

Hash (cost=1.81..1.81 rows=131 width=61) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on custominfo (cost=0.00..1.81 rows=131 width=61) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.50..56.51 rows=1 width=40) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Only Scan using opendoorrecord_lockid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.42..42.89 rows=363 width=20) (actual rows= loops=)

  • Index Cond: (lockid = (lockinfo.lockid)::text)
54. 0.000 0.000 ↓ 0.0

Index Scan using _regulated_house_alemp_pkey on _regulated_house_alemp (cost=0.15..0.17 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((houseinfo_1.houseid)::text = houseid)
55. 0.000 0.000 ↓ 0.0

Append (cost=477.12..956.56 rows=2 width=32) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Aggregate (cost=477.12..477.13 rows=1 width=32) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.80..473.99 rows=2 width=59) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.51..473.37 rows=2 width=44) (actual rows= loops=)

  • Hash Cond: ((lockkeyinfo_2.lockid)::text = (lockinfo_1.lockid)::text)
59. 0.000 0.000 ↓ 0.0

Seq Scan on lockkeyinfo lockkeyinfo_2 (cost=0.00..453.05 rows=7,536 width=44) (actual rows= loops=)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
60. 0.000 0.000 ↓ 0.0

Hash (cost=0.50..0.50 rows=1 width=19) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((houseid)::text = (houseinfo_1.houseid)::text)
62. 0.000 0.000 ↓ 0.0

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

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

SubPlan (for Aggregate)

64. 0.000 0.000 ↓ 0.0

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

65.          

Initplan (for Result)

66. 0.000 0.000 ↓ 0.0

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

67. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: ((userid = (appuseraccount_1.account)::text) AND (lockid = (lockkeyinfo_2.lockid)::text) AND (opentime IS NOT NULL))
68. 0.000 0.000 ↓ 0.0

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

69.          

Initplan (for Result)

70. 0.000 0.000 ↓ 0.0

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

71. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: ((userid = (appuseraccount_1.account)::text) AND (lockid = (lockkeyinfo_2.lockid)::text) AND (opentime IS NOT NULL))
72. 0.000 0.000 ↓ 0.0

Aggregate (cost=479.38..479.39 rows=1 width=32) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.51..477.81 rows=1 width=178) (actual rows= loops=)

  • Join Filter: ((COALESCE(lockkeyinfo_3.keyholder, lockkeyinfo_3.useraccount))::text = (bluekeyuserinfo_1.bluekeyaccount)::text)
74. 0.000 0.000 ↓ 0.0

Seq Scan on bluekeyuserinfo bluekeyuserinfo_1 (cost=0.00..1.41 rows=101 width=158) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Materialize (cost=0.51..473.38 rows=2 width=44) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.51..473.37 rows=2 width=44) (actual rows= loops=)

  • Hash Cond: ((lockkeyinfo_3.lockid)::text = (lockinfo_2.lockid)::text)
77. 0.000 0.000 ↓ 0.0

Seq Scan on lockkeyinfo lockkeyinfo_3 (cost=0.00..453.05 rows=7,536 width=44) (actual rows= loops=)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
78. 0.000 0.000 ↓ 0.0

Hash (cost=0.50..0.50 rows=1 width=19) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

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

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

SubPlan (for Aggregate)

81. 0.000 0.000 ↓ 0.0

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

82.          

Initplan (for Result)

83. 0.000 0.000 ↓ 0.0

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

84. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: ((userid = (bluekeyuserinfo_1.bluekeyaccount)::text) AND (lockid = (lockkeyinfo_3.lockid)::text) AND (opentime IS NOT NULL))
85. 0.000 0.000 ↓ 0.0

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

86.          

Initplan (for Result)

87. 0.000 0.000 ↓ 0.0

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

88. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: ((userid = (bluekeyuserinfo_1.bluekeyaccount)::text) AND (lockid = (lockkeyinfo_3.lockid)::text) AND (opentime IS NOT NULL))