explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 16lg

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

Append (cost=37,811.65..127,875.71 rows=16,298 width=1,640) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=37,811.65..38,608.34 rows=15,175 width=937) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=37,811.65..38,456.59 rows=15,175 width=937) (actual rows= loops=)

  • Group Key: (COALESCE(user_info.headpicture, bk.certfrontpic, bk.certbackpic)), (COALESCE(user_info.username, bk.username)), (CASE WHEN (substr((COALESCE(bk.certid, user_info.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, user_info.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, user_info.userid) END), user_info.authstate, COALESCE(user_info.account, bk.keyholder), lk.authtime, CASE WHEN (lk.valid_state = '1'::text) THEN lk.stoptime WHEN (lk.valid_state = '0'::text) THEN lk.expireddate ELSE NULL::character varying END, lk.valid_state, lk.authaccount, lk.keytype, hi.detailaddr, hi.housenumber, xz.""全称"", hi.xzqhdm, CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END, CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""名称"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位名称"" ELSE NULL::text END, '长租房'::text, li.lockid, (pipelinedb.finalize('extensions.hll_count_distinct'::text, '{pg_catalog.any}'::text[], opendoor_summary_mrel.distinct_open_counts, NULL::bigint)), lk.useraccount, hi.jwq, ((opendoor_summary_mrel._0)::timestamp(0) without time zone)::text
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,372.27..36,977.03 rows=15,175 width=937) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=16,371.85..28,538.22 rows=15,175 width=898) (actual rows= loops=)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,084.56..22,083.22 rows=219,343 width=955) (actual rows= loops=)

  • Hash Cond: (((lk.lockid)::text = (bk.lockid)::text) AND ((lk.useraccount)::text = (bk.bluekeyaccount)::text))
  • Filter: ((COALESCE(bk.certid, user_info.userid, user_info.account) IS NOT NULL) AND (CASE WHEN (substr((COALESCE(bk.certid, user_info.userid, user_info.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, user_info.userid, user_info.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, user_info.userid, user_info.account) END IS NOT NULL))
7.          

CTE all_key

8. 0.000 0.000 ↓ 0.0

Append (cost=0.00..9,984.76 rows=224,910 width=232) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on lockkeyinfo lk_2 (cost=0.00..7,371.76 rows=120,415 width=238) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on keyhistoryinfo khi (cost=0.00..1,488.45 rows=104,495 width=225) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,998.38..8,640.45 rows=221,553 width=522) (actual rows= loops=)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
12. 0.000 0.000 ↓ 0.0

CTE Scan on all_key lk (cost=0.00..5,060.47 rows=221,553 width=398) (actual rows= loops=)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
13. 0.000 0.000 ↓ 0.0

Hash (cost=1,703.50..1,703.50 rows=103,590 width=124) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on appuseraccount user_info (cost=0.00..1,703.50 rows=103,590 width=124) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=48.73..48.73 rows=3,513 width=280) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on bluekeyuserinfo bk (cost=0.00..48.73 rows=3,513 width=280) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=3,246.16..3,246.16 rows=3,290 width=235) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Gather (cost=2,341.27..3,246.16 rows=3,290 width=235) (actual rows= loops=)

  • Workers Planned: 1
19. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=1,341.27..1,917.16 rows=1,935 width=235) (actual rows= loops=)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
20. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on lockinfo li (cost=0.00..462.93 rows=27,973 width=51) (actual rows= loops=)

  • Filter: (lockid IS NOT NULL)
21. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=1,316.20..1,316.20 rows=2,006 width=249) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=102.26..1,316.20 rows=2,006 width=249) (actual rows= loops=)

  • Hash Cond: (ys."管理单位代码_id" = gl.id)
  • Filter: ((CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END IS NOT NULL) AND (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END IS NOT NULL) AND (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=73.01..1,209.55 rows=28,950 width=191) (actual rows= loops=)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
24. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on houseinfo hi (cost=0.00..740.60 rows=28,950 width=143) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=53.53..53.53 rows=1,559 width=60) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual rows= loops=)

  • Hash Cond: (ys."行政区划_id" = xz.id)
27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

Hash (cost=17.79..17.79 rows=1,559 width=60) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on "行政区划代码" xz (cost=0.00..17.79 rows=1,559 width=60) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=14.22..14.22 rows=1,202 width=90) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on "管理单位代码" gl (cost=0.00..14.22 rows=1,202 width=90) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel (cost=0.42..0.52 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (((li.lockid)::text = lockid) AND (userid = (lk.useraccount)::text))
33. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=89,112.89..89,185.88 rows=1,123 width=895) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Unique (cost=89,112.89..89,174.65 rows=1,123 width=895) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=89,112.89..89,115.70 rows=1,123 width=895) (actual rows= loops=)

  • Sort Key: (COALESCE(user_info_1.headpicture, bk_1.certfrontpic, bk_1.certbackpic)), (COALESCE(user_info_1.username, bk_1.username)), (COALESCE(bk_1.certid, user_info_1.userid)), user_info_1.authstate, (COALESCE(user_info_1.account, bk_1.keyholder)), lk_1.authtime, (CASE WHEN (lk_1.valid_state = '1'::text) THEN lk_1.stoptime WHEN (lk_1.valid_state = '0'::text) THEN lk_1.expireddate ELSE NULL::character varying END), lk_1.valid_state, lk_1.authaccount, lk_1.keytype, hi_1.detailaddr, hi_1.housenumber, xz_1.""全称"", hi_1.xzqhdm, (CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1.""代码"" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1.""上级单位代码"" ELSE NULL::text END), (CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1.""名称"" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1.""上级单位名称"" ELSE NULL::text END), (CASE WHEN ((hi_1.housecategory)::text = '0'::text) THEN '网约房'::text WHEN ((hi_1.housecategory)::text = '2'::text) THEN '企业宿舍'::text ELSE NULL::text END), li_1.lockid, (count(DISTINCT opendoorrecord.opentime)), lk_1.useraccount, (max((opendoorrecord.opentime)::text))
36. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=79,600.36..89,055.99 rows=1,123 width=895) (actual rows= loops=)

  • Merge Cond: (((li_1.lockid)::text = (opendoorrecord.lockid)::text) AND ((lk_1.useraccount)::text = (opendoorrecord.userid)::text))
37. 0.000 0.000 ↓ 0.0

Sort (cost=2,354.44..2,357.25 rows=1,123 width=853) (actual rows= loops=)

  • Sort Key: li_1.lockid, lk_1.useraccount
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,398.39..2,297.54 rows=1,123 width=853) (actual rows= loops=)

  • Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
39. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,106.37..1,721.63 rows=19,830 width=932) (actual rows= loops=)

  • Hash Cond: (((lk_1.lockid)::text = (bk_1.lockid)::text) AND ((lk_1.useraccount)::text = (bk_1.bluekeyaccount)::text))
  • Filter: ((COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL) AND (COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL))
40.          

CTE all_key

41. 0.000 0.000 ↓ 0.0

Append (cost=0.00..882.05 rows=20,333 width=233) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on lockkeyinfo lk_3 (cost=0.00..640.44 rows=10,218 width=239) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on keyhistoryinfo khi_1 (cost=0.00..139.95 rows=10,115 width=226) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=222.12..732.21 rows=20,030 width=518) (actual rows= loops=)

  • Hash Cond: ((lk_1.useraccount)::text = (user_info_1.account)::text)
45. 0.000 0.000 ↓ 0.0

CTE Scan on all_key lk_1 (cost=0.00..457.49 rows=20,030 width=398) (actual rows= loops=)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
46. 0.000 0.000 ↓ 0.0

Hash (cost=117.94..117.94 rows=8,334 width=120) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on appuseraccount user_info_1 (cost=0.00..117.94 rows=8,334 width=120) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=76 width=367) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..1.06 rows=76 width=367) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=289.37..289.37 rows=212 width=189) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash Join (cost=223.56..289.37 rows=212 width=189) (actual rows= loops=)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
52. 0.000 0.000 ↓ 0.0

Seq Scan on lockinfo li_1 (cost=0.00..49.65 rows=3,745 width=41) (actual rows= loops=)

  • Filter: (lockid IS NOT NULL)
53. 0.000 0.000 ↓ 0.0

Hash (cost=220.20..220.20 rows=269 width=203) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=102.26..220.20 rows=269 width=203) (actual rows= loops=)

  • Hash Cond: (ys_1."管理单位代码_id" = gl_1.id)
  • Filter: ((CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1.""代码"" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1.""上级单位代码"" ELSE NULL::text END IS NOT NULL) AND (CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1.""代码"" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1.""上级单位代码"" ELSE NULL::text END IS NOT NULL) AND (CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1.""代码"" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1.""上级单位代码"" ELSE NULL::text END = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))
55. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=73.01..180.58 rows=3,885 width=145) (actual rows= loops=)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
56. 0.000 0.000 ↓ 0.0

Seq Scan on houseinfo hi_1 (cost=0.00..80.74 rows=3,885 width=97) (actual rows= loops=)

  • Filter: ((housecategory)::text = ANY ('{0,2}'::text[]))
57. 0.000 0.000 ↓ 0.0

Hash (cost=53.53..53.53 rows=1,559 width=60) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual rows= loops=)

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
59. 0.000 0.000 ↓ 0.0

Seq Scan on "行政区划与管理单位代码映射" ys_1 (cost=0.00..13.11 rows=1,191 width=32) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=17.79..17.79 rows=1,559 width=60) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on "行政区划代码" xz_1 (cost=0.00..17.79 rows=1,559 width=60) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=14.22..14.22 rows=1,202 width=90) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on "管理单位代码" gl_1 (cost=0.00..14.22 rows=1,202 width=90) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=77,245.92..85,688.64 rows=64,655 width=103) (actual rows= loops=)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
65. 0.000 0.000 ↓ 0.0

Sort (cost=77,245.92..78,805.15 rows=623,694 width=51) (actual rows= loops=)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid
66. 0.000 0.000 ↓ 0.0

Seq Scan on opendoorrecord (cost=0.00..17,213.88 rows=623,694 width=51) (actual rows= loops=)

  • Filter: (((opentime)::text >= '2018-04-25'::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))