explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vTkc

Settings
# exclusive inclusive rows x rows loops node
1. 12.710 5,210.585 ↓ 7.1 115,660 1
→

Append (cost=37,816.03..127,888.85 rows=16,300 width=1,640) (actual time=2,782.120..5,210.585 rows=115,660 loops=1)

2. 25.570 2,887.916 ↓ 7.5 113,958 1
→

Subquery Scan on *SELECT* 1 (cost=37,816.03..38,612.82 rows=15,177 width=937) (actual time=2,782.120..2,887.916 rows=113,958 loops=1)

3. 369.448 2,862.346 ↓ 7.5 113,958 1
→

HashAggregate (cost=37,816.03..38,461.05 rows=15,177 width=937) (actual time=2,782.117..2,862.346 rows=113,958 loops=1)

  • 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. 205.887 2,492.898 ↓ 7.6 115,146 1
→

Nested Loop Left Join (cost=16,373.99..36,981.29 rows=15,177 width=937) (actual time=320.898..2,492.898 rows=115,146 loops=1)

5. 137.106 1,480.989 ↓ 7.6 115,146 1
→

Hash Join (cost=16,373.57..28,541.37 rows=15,177 width=898) (actual time=320.847..1,480.989 rows=115,146 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
6. 234.602 1,117.471 ↑ 1.4 160,548 1
→

Hash Left Join (cost=13,086.29..22,085.99 rows=219,369 width=955) (actual time=94.425..1,117.471 rows=160,548 loops=1)

  • 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))
  • Rows Removed by Filter: 12911
7.          

CTE all_key
8. 30.594 389.983 ↓ 1.0 230,229 1
→

Append (cost=0.00..9,986.49 rows=224,936 width=232) (actual time=0.044..389.983 rows=230,229 loops=1)

9. 298.306 298.306 ↑ 1.0 120,364 1
→

Seq Scan on lockkeyinfo lk_2 (cost=0.00..7,373.36 rows=120,441 width=238) (actual time=0.042..298.306 rows=120,364 loops=1)

10. 61.083 61.083 ↓ 1.1 109,865 1
→

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

11. 153.591 880.429 ↑ 1.3 173,459 1
→

Hash Left Join (cost=2,998.38..8,641.10 rows=221,579 width=522) (actual time=91.964..880.429 rows=173,459 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
12. 635.030 635.030 ↑ 1.3 173,459 1
→

CTE Scan on all_key lk (cost=0.00..5,061.06 rows=221,579 width=398) (actual time=0.052..635.030 rows=173,459 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
  • Rows Removed by Filter: 56770
13. 39.843 91.808 ↑ 1.0 103,568 1
→

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

  • Buckets: 131072 Batches: 1 Memory Usage: 16434kB
14. 51.965 51.965 ↑ 1.0 103,568 1
→

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

15. 1.339 2.440 ↓ 1.0 3,550 1
→

Hash (cost=48.73..48.73 rows=3,513 width=280) (actual time=2.440..2.440 rows=3,550 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 877kB
16. 1.101 1.101 ↓ 1.0 3,550 1
→

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

17. 19.384 226.412 ↓ 10.2 33,638 1
→

Hash (cost=3,246.16..3,246.16 rows=3,290 width=235) (actual time=226.412..226.412 rows=33,638 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 9571kB
18. 13.328 207.028 ↓ 10.2 33,638 1
→

Gather (cost=2,341.27..3,246.16 rows=3,290 width=235) (actual time=159.758..207.028 rows=33,638 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
19. 27.839 193.700 ↓ 8.7 16,819 2
→

Parallel Hash Join (cost=1,341.27..1,917.16 rows=1,935 width=235) (actual time=154.866..193.700 rows=16,819 loops=2)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
20. 11.396 11.396 ↑ 1.2 23,580 2
→

Parallel Seq Scan on lockinfo li (cost=0.00..462.93 rows=27,973 width=51) (actual time=0.007..11.396 rows=23,580 loops=2)

  • Filter: (lockid IS NOT NULL)
21. 30.673 154.465 ↓ 12.4 24,810 2
→

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

  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 15104kB
22. 68.457 123.792 ↓ 12.4 24,810 2
→

Hash Left Join (cost=102.26..1,316.20 rows=2,006 width=249) (actual time=3.721..123.792 rows=24,810 loops=2)

  • 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[])))
  • Rows Removed by Filter: 10119
23. 36.410 54.444 ↓ 1.2 34,928 2
→

Hash Left Join (cost=73.01..1,209.55 rows=28,950 width=191) (actual time=2.809..54.444 rows=34,928 loops=2)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
24. 15.247 15.247 ↓ 1.2 34,928 2
→

Parallel Seq Scan on houseinfo hi (cost=0.00..740.60 rows=28,950 width=143) (actual time=0.010..15.247 rows=34,928 loops=2)

25. 0.623 2.787 ↓ 1.0 1,563 2
→

Hash (cost=53.53..53.53 rows=1,559 width=60) (actual time=2.787..2.787 rows=1,563 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
26. 0.862 2.164 ↓ 1.0 1,563 2
→

Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual time=1.171..2.164 rows=1,563 loops=2)

  • Hash Cond: (ys."行政区划_id" = xz.id)
27. 0.243 0.243 ↓ 1.1 1,280 2
→

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

28. 0.588 1.059 ↑ 1.0 1,546 2
→

Hash (cost=17.79..17.79 rows=1,559 width=60) (actual time=1.058..1.059 rows=1,546 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
29. 0.471 0.471 ↑ 1.0 1,546 2
→

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

30. 0.512 0.891 ↑ 1.0 1,202 2
→

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
31. 0.379 0.379 ↑ 1.0 1,202 2
→

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

32. 806.022 806.022 ↑ 1.0 1 115,146
→

Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel (cost=0.42..0.52 rows=1 width=46) (actual time=0.007..0.007 rows=1 loops=115,146)

  • Index Cond: (((li.lockid)::text = lockid) AND (userid = (lk.useraccount)::text))
33. 0.652 2,309.959 ↓ 1.5 1,702 1
→

Subquery Scan on *SELECT* 2 (cost=89,121.53..89,194.53 rows=1,123 width=895) (actual time=2,308.384..2,309.959 rows=1,702 loops=1)

34. 0.773 2,309.307 ↓ 1.5 1,702 1
→

Unique (cost=89,121.53..89,183.30 rows=1,123 width=895) (actual time=2,308.381..2,309.307 rows=1,702 loops=1)

35. 3.020 2,308.534 ↓ 1.5 1,702 1
→

Sort (cost=89,121.53..89,124.34 rows=1,123 width=895) (actual time=2,308.379..2,308.534 rows=1,702 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 926kB
36. 5.709 2,305.514 ↓ 1.5 1,702 1
→

Merge Left Join (cost=79,608.09..89,064.63 rows=1,123 width=895) (actual time=1,621.984..2,305.514 rows=1,702 loops=1)

  • Merge Cond: (((li_1.lockid)::text = (opendoorrecord.lockid)::text) AND ((lk_1.useraccount)::text = (opendoorrecord.userid)::text))
37. 4.166 90.407 ↓ 1.5 1,702 1
→

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

  • Sort Key: li_1.lockid, lk_1.useraccount
  • Sort Method: quicksort Memory: 926kB
38. 3.201 86.241 ↓ 1.5 1,702 1
→

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

  • Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
39. 6.681 70.443 ↑ 1.7 11,757 1
→

Hash Left Join (cost=1,106.37..1,721.63 rows=19,830 width=932) (actual time=5.442..70.443 rows=11,757 loops=1)

  • 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))
  • Rows Removed by Filter: 885
40.          

CTE all_key
41. 2.483 31.834 ↓ 1.0 20,407 1
→

Append (cost=0.00..882.05 rows=20,333 width=233) (actual time=0.027..31.834 rows=20,407 loops=1)

42. 24.534 24.534 ↓ 1.0 10,292 1
→

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

43. 4.817 4.817 ↑ 1.0 10,115 1
→

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

44. 7.374 63.696 ↑ 1.6 12,642 1
→

Hash Left Join (cost=222.12..732.21 rows=20,030 width=518) (actual time=5.364..63.696 rows=12,642 loops=1)

  • Hash Cond: ((lk_1.useraccount)::text = (user_info_1.account)::text)
45. 51.010 51.010 ↑ 1.6 12,642 1
→

CTE Scan on all_key lk_1 (cost=0.00..457.49 rows=20,030 width=398) (actual time=0.032..51.010 rows=12,642 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
  • Rows Removed by Filter: 7765
46. 2.252 5.312 ↓ 1.0 8,338 1
→

Hash (cost=117.94..117.94 rows=8,334 width=120) (actual time=5.312..5.312 rows=8,338 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 865kB
47. 3.060 3.060 ↓ 1.0 8,338 1
→

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

48. 0.035 0.066 ↓ 1.0 77 1
→

Hash (cost=1.06..1.06 rows=76 width=367) (actual time=0.066..0.066 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
49. 0.031 0.031 ↓ 1.0 77 1
→

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..1.06 rows=76 width=367) (actual time=0.007..0.031 rows=77 loops=1)

50. 0.427 12.597 ↓ 4.6 973 1
→

Hash (cost=289.37..289.37 rows=212 width=189) (actual time=12.597..12.597 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 232kB
51. 1.289 12.170 ↓ 4.6 973 1
→

Hash Join (cost=223.56..289.37 rows=212 width=189) (actual time=10.091..12.170 rows=973 loops=1)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
52. 0.826 0.826 ↑ 1.0 3,707 1
→

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

  • Filter: (lockid IS NOT NULL)
53. 0.620 10.055 ↓ 4.3 1,162 1
→

Hash (cost=220.20..220.20 rows=269 width=203) (actual time=10.055..10.055 rows=1,162 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 297kB
54. 2.750 9.435 ↓ 4.3 1,162 1
→

Hash Left Join (cost=102.26..220.20 rows=269 width=203) (actual time=2.493..9.435 rows=1,162 loops=1)

  • 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[])))
  • Rows Removed by Filter: 2680
55. 1.488 6.053 ↑ 1.0 3,842 1
→

Hash Left Join (cost=73.01..180.58 rows=3,885 width=145) (actual time=1.836..6.053 rows=3,842 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
56. 2.754 2.754 ↑ 1.0 3,842 1
→

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

  • Filter: ((housecategory)::text = ANY ('{0,2}'::text[]))
  • Rows Removed by Filter: 896
57. 0.405 1.811 ↓ 1.0 1,563 1
→

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

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
58. 0.551 1.406 ↓ 1.0 1,563 1
→

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

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
59. 0.165 0.165 ↓ 1.1 1,280 1
→

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

60. 0.377 0.690 ↑ 1.0 1,546 1
→

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

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
61. 0.313 0.313 ↑ 1.0 1,546 1
→

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

62. 0.360 0.632 ↑ 1.0 1,202 1
→

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
63. 0.272 0.272 ↑ 1.0 1,202 1
→

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

64. 545.320 2,209.398 ↑ 4.6 13,913 1
→

GroupAggregate (cost=77,253.65..85,697.18 rows=64,662 width=103) (actual time=1,530.875..2,209.398 rows=13,913 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
65. 1,130.877 1,664.078 ↓ 1.0 631,046 1
→

Sort (cost=77,253.65..78,813.03 rows=623,753 width=51) (actual time=1,530.513..1,664.078 rows=631,046 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid
  • Sort Method: external merge Disk: 37744kB
66. 533.201 533.201 ↓ 1.0 631,189 1
→

Seq Scan on opendoorrecord (cost=0.00..17,215.50 rows=623,753 width=51) (actual time=0.027..533.201 rows=631,189 loops=1)

  • Filter: (((opentime)::text >= '2018-04-25'::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 15397
Planning time : 5.326 ms
Execution time : 5,232.293 ms