explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aI9i

Settings
# exclusive inclusive rows x rows loops node
1. 16.105 5,938.094 ↑ 1.7 136,302 1

Append (cost=74,519.67..182,501.47 rows=232,892 width=1,640) (actual time=3,054.661..5,938.094 rows=136,302 loops=1)

2. 104.379 3,237.965 ↑ 1.6 131,570 1

Unique (cost=74,519.67..86,445.43 rows=216,832 width=937) (actual time=3,054.660..3,237.965 rows=131,570 loops=1)

3. 732.775 3,133.586 ↑ 1.6 132,925 1

Sort (cost=74,519.67..75,061.75 rows=216,832 width=937) (actual time=3,054.655..3,133.586 rows=132,925 loops=1)

  • Sort 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), 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)
  • Sort Method: external merge Disk: 60072kB
4. 259.973 2,400.811 ↑ 1.6 132,925 1

Hash Left Join (cost=27,807.46..50,177.22 rows=216,832 width=937) (actual time=894.212..2,400.811 rows=132,925 loops=1)

  • Hash Cond: (((li.lockid)::text = opendoor_summary_mrel.lockid) AND ((lk.useraccount)::text = opendoor_summary_mrel.userid))
5. 149.102 1,665.263 ↑ 1.6 132,925 1

Hash Join (cost=17,349.24..31,533.59 rows=216,832 width=898) (actual time=417.690..1,665.263 rows=132,925 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
6. 254.008 1,272.397 ↑ 1.4 160,555 1

Hash Left Join (cost=13,086.29..22,085.99 rows=219,369 width=955) (actual time=173.876..1,272.397 rows=160,555 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: 12912
7.          

CTE all_key

8. 32.564 423.061 ↓ 1.0 230,239 1

Append (cost=0.00..9,986.49 rows=224,936 width=232) (actual time=0.021..423.061 rows=230,239 loops=1)

9. 332.222 332.222 ↑ 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.020..332.222 rows=120,364 loops=1)

10. 58.275 58.275 ↓ 1.1 109,875 1

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

11. 153.662 1,014.084 ↑ 1.3 173,467 1

Hash Left Join (cost=2,998.38..8,641.10 rows=221,579 width=522) (actual time=169.531..1,014.084 rows=173,467 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
12. 691.016 691.016 ↑ 1.3 173,467 1

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

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
  • Rows Removed by Filter: 56772
13. 81.820 169.406 ↑ 1.0 103,570 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 16434kB
14. 87.586 87.586 ↑ 1.0 103,570 1

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

15. 2.498 4.305 ↓ 1.0 3,550 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 877kB
16. 1.807 1.807 ↓ 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.807 rows=3,550 loops=1)

17. 29.186 243.764 ↑ 1.1 43,112 1

Hash (cost=3,675.40..3,675.40 rows=47,004 width=235) (actual time=243.764..243.764 rows=43,112 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 12080kB
18. 49.346 214.578 ↑ 1.1 43,112 1

Hash Left Join (cost=2,116.64..3,675.40 rows=47,004 width=235) (actual time=71.750..214.578 rows=43,112 loops=1)

  • 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))
  • Rows Removed by Filter: 2029
19. 32.204 164.625 ↑ 1.1 45,141 1

Hash Left Join (cost=2,087.39..3,520.31 rows=47,478 width=177) (actual time=71.132..164.625 rows=45,141 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
20. 46.274 130.520 ↑ 1.1 45,141 1

Hash Join (cost=2,014.38..2,797.96 rows=47,478 width=129) (actual time=69.218..130.520 rows=45,141 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
21. 15.132 15.132 ↑ 1.0 47,163 1

Seq Scan on lockinfo li (cost=0.00..658.74 rows=47,554 width=51) (actual time=0.010..15.132 rows=47,163 loops=1)

  • Filter: (lockid IS NOT NULL)
22. 29.154 69.114 ↓ 1.0 69,857 1

Hash (cost=1,145.89..1,145.89 rows=69,479 width=143) (actual time=69.114..69.114 rows=69,857 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 12601kB
23. 39.960 39.960 ↓ 1.0 69,857 1

Seq Scan on houseinfo hi (cost=0.00..1,145.89 rows=69,479 width=143) (actual time=0.006..39.960 rows=69,857 loops=1)

24. 0.424 1.901 ↓ 1.0 1,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
25. 0.540 1.477 ↓ 1.0 1,563 1

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

  • Hash Cond: (ys."行政区划_id" = xz.id)
26. 0.163 0.163 ↓ 1.1 1,280 1

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

27. 0.463 0.774 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
28. 0.311 0.311 ↑ 1.0 1,546 1

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

29. 0.348 0.607 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
30. 0.259 0.259 ↑ 1.0 1,202 1

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

31. 133.760 475.575 ↓ 1.1 196,517 1

Hash (cost=7,857.10..7,857.10 rows=173,408 width=46) (actual time=475.574..475.575 rows=196,517 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 17428kB
32. 341.815 341.815 ↓ 1.2 199,554 1

Seq Scan on opendoor_summary_mrel (cost=0.00..7,857.10 rows=173,408 width=46) (actual time=0.041..341.815 rows=199,554 loops=1)

33. 3.317 2,684.024 ↑ 3.4 4,732 1

Subquery Scan on *SELECT* 2 (cost=91,679.35..92,723.25 rows=16,060 width=895) (actual time=2,676.350..2,684.024 rows=4,732 loops=1)

34. 3.405 2,680.707 ↑ 3.4 4,732 1

Unique (cost=91,679.35..92,562.65 rows=16,060 width=895) (actual time=2,676.345..2,680.707 rows=4,732 loops=1)

35. 15.541 2,677.302 ↑ 3.4 4,733 1

Sort (cost=91,679.35..91,719.50 rows=16,060 width=895) (actual time=2,676.341..2,677.302 rows=4,733 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: 2633kB
36. 12.705 2,661.761 ↑ 3.4 4,733 1

Hash Right Join (cost=80,001.72..90,557.47 rows=16,060 width=895) (actual time=1,869.341..2,661.761 rows=4,733 loops=1)

  • Hash Cond: (((opendoorrecord.lockid)::text = (li_1.lockid)::text) AND ((opendoorrecord.userid)::text = (lk_1.useraccount)::text))
37. 621.678 2,540.191 ↑ 4.6 13,922 1

GroupAggregate (cost=77,253.65..85,697.18 rows=64,662 width=103) (actual time=1,760.435..2,540.191 rows=13,922 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
38. 1,296.293 1,918.513 ↓ 1.0 631,208 1

Sort (cost=77,253.65..78,813.03 rows=623,753 width=51) (actual time=1,759.964..1,918.513 rows=631,208 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid
  • Sort Method: external merge Disk: 37752kB
39. 622.220 622.220 ↓ 1.0 631,208 1

Seq Scan on opendoorrecord (cost=0.00..17,215.50 rows=623,753 width=51) (actual time=0.018..622.220 rows=631,208 loops=1)

  • Filter: (((opentime)::text >= '2018-04-25'::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 15397
40. 4.351 108.865 ↑ 3.4 4,733 1

Hash (cost=2,507.18..2,507.18 rows=16,060 width=853) (actual time=108.865..108.865 rows=4,733 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2101kB
41. 6.095 104.514 ↑ 3.4 4,733 1

Hash Left Join (cost=1,434.39..2,507.18 rows=16,060 width=853) (actual time=14.239..104.514 rows=4,733 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))
  • Rows Removed by Filter: 4828
42. 4.376 97.817 ↑ 1.7 9,561 1

Hash Left Join (cost=1,405.14..2,434.94 rows=16,224 width=795) (actual time=13.563..97.817 rows=9,561 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
43. 6.396 91.597 ↑ 1.7 9,561 1

Hash Join (cost=1,332.13..2,249.90 rows=16,224 width=747) (actual time=11.713..91.597 rows=9,561 loops=1)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
44. 6.144 81.639 ↑ 1.8 10,795 1

Hash Join (cost=1,202.83..2,068.50 rows=19,830 width=705) (actual time=8.141..81.639 rows=10,795 loops=1)

  • Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
45. 7.254 73.185 ↑ 1.7 11,757 1

Hash Left Join (cost=1,106.37..1,721.63 rows=19,830 width=932) (actual time=5.819..73.185 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
46.          

CTE all_key

47. 2.549 32.907 ↓ 1.0 20,407 1

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

48. 25.694 25.694 ↓ 1.0 10,292 1

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

49. 4.664 4.664 ↑ 1.0 10,115 1

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

50. 8.274 65.864 ↑ 1.6 12,642 1

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

  • Hash Cond: ((lk_1.useraccount)::text = (user_info_1.account)::text)
51. 51.915 51.915 ↑ 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.044..51.915 rows=12,642 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
  • Rows Removed by Filter: 7765
52. 2.348 5.675 ↓ 1.0 8,338 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 865kB
53. 3.327 3.327 ↓ 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.007..3.327 rows=8,338 loops=1)

54. 0.035 0.067 ↓ 1.0 77 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
55. 0.032 0.032 ↓ 1.0 77 1

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

56. 0.998 2.310 ↑ 1.0 3,707 1

Hash (cost=49.65..49.65 rows=3,745 width=41) (actual time=2.310..2.310 rows=3,707 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 305kB
57. 1.312 1.312 ↑ 1.0 3,707 1

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

  • Filter: (lockid IS NOT NULL)
58. 1.142 3.562 ↑ 1.0 3,842 1

Hash (cost=80.74..80.74 rows=3,885 width=97) (actual time=3.562..3.562 rows=3,842 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 507kB
59. 2.420 2.420 ↑ 1.0 3,842 1

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

  • Filter: ((housecategory)::text = ANY ('{0,2}'::text[]))
  • Rows Removed by Filter: 896
60. 0.456 1.844 ↓ 1.0 1,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
61. 0.545 1.388 ↓ 1.0 1,563 1

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

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
62. 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)

63. 0.369 0.678 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
64. 0.309 0.309 ↑ 1.0 1,546 1

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

65. 0.340 0.602 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
66. 0.262 0.262 ↑ 1.0 1,202 1

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

Planning time : 5.076 ms
Execution time : 5,964.112 ms