explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hMvu

Settings
# exclusive inclusive rows x rows loops node
1. 0.311 8,222.033 ↑ 931,592.0 18 1

Nested Loop (cost=41,502.48..329,612,547.53 rows=16,768,656 width=88) (actual time=1,459.962..8,222.033 rows=18 loops=1)

2.          

CTE activi

3. 74.730 518.771 ↑ 848.4 18 1

GroupAggregate (cost=17,510.37..19,953.94 rows=15,272 width=80) (actual time=447.649..518.771 rows=18 loops=1)

  • Group Key: (COALESCE(CASE WHEN (substr((COALESCE(bk_1.certid, ac.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac.userid) END, ac.account))
  • Filter: (count(DISTINCT li_1.lockid) FILTER (WHERE ((COALESCE((CASE WHEN (((lk_1.applytime)::text >= (lk_1.authtime)::text) AND (lk_1.keystate = 1)) THEN lk_1.applytime WHEN (((lk_1.applytime)::text < (lk_1.authtime)::text) AND (lk_1.keystate = 1)) THEN NULL::character varying WHEN (((lk_1.expireddate)::date < CURRENT_DATE) AND (lk_1.keystate = 0)) THEN lk_1.expireddate ELSE NULL::character varying END), lk_1.expireddate))::date <= CURRENT_DATE)) >= 5)
  • Rows Removed by Filter: 14312
4. 38.150 444.041 ↑ 2.6 17,509 1

Sort (cost=17,510.37..17,624.92 rows=45,817 width=121) (actual time=440.861..444.041 rows=17,509 loops=1)

  • Sort Key: (COALESCE(CASE WHEN (substr((COALESCE(bk_1.certid, ac.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac.userid) END, ac.account))
  • Sort Method: quicksort Memory: 3231kB
5. 22.911 405.891 ↑ 2.6 17,509 1

Gather (cost=4,790.69..13,963.32 rows=45,817 width=121) (actual time=160.778..405.891 rows=17,509 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 17.335 382.980 ↑ 3.3 5,836 3

Hash Left Join (cost=3,790.69..8,381.62 rows=19,090 width=121) (actual time=146.666..382.980 rows=5,836 loops=3)

  • 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)
  • Rows Removed by Filter: 248
7. 10.324 363.676 ↑ 3.2 6,085 3

Hash Left Join (cost=3,761.44..8,110.77 rows=19,187 width=153) (actual time=144.677..363.676 rows=6,085 loops=3)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
8. 9.774 348.511 ↑ 3.2 6,085 3

Parallel Hash Join (cost=3,688.43..7,775.25 rows=19,187 width=137) (actual time=139.819..348.511 rows=6,085 loops=3)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
9. 8.360 292.903 ↑ 3.1 6,169 3

Parallel Hash Join (cost=2,581.48..6,617.85 rows=19,220 width=157) (actual time=91.181..292.903 rows=6,169 loops=3)

  • Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
10. 11.940 261.158 ↑ 3.1 6,169 3

Hash Left Join (cost=1,766.76..5,752.68 rows=19,220 width=125) (actual time=67.481..261.158 rows=6,169 loops=3)

  • Hash Cond: (((lk_1.lockid)::text = (bk_1.lockid)::text) AND (((COALESCE(lk_1.keyholder, lk_1.useraccount)))::text = (bk_1.bluekeyaccount)::text))
  • Filter: (COALESCE(CASE WHEN (substr((COALESCE(bk_1.certid, ac.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac.userid) END, ac.account) IS NOT NULL)
  • Rows Removed by Filter: 446
11. 11.556 245.538 ↑ 2.9 6,614 3

Parallel Hash Left Join (cost=1,664.03..5,548.54 rows=19,316 width=154) (actual time=63.760..245.538 rows=6,614 loops=3)

  • Hash Cond: (((COALESCE(lk_1.keyholder, lk_1.useraccount)))::text = (ac.account)::text)
12. 0.000 172.100 ↑ 2.9 6,614 3

Parallel Append (cost=0.00..3,833.80 rows=19,316 width=124) (actual time=0.061..172.100 rows=6,614 loops=3)

13. 111.316 111.316 ↑ 2.0 5,250 3

Parallel Seq Scan on lockkeyinfo lk_1 (cost=0.00..2,096.39 rows=10,710 width=129) (actual time=0.052..111.316 rows=5,250 loops=3)

  • Filter: ((userrole <> 255) AND (keytype = ANY ('{0,2}'::integer[])) AND ((CURRENT_DATE - (authtime)::date) <= 30))
  • Rows Removed by Filter: 35177
14. 86.764 86.764 ↑ 4.2 2,046 2

Parallel Seq Scan on keyhistoryinfo hk (cost=0.00..1,640.84 rows=8,606 width=117) (actual time=0.049..86.764 rows=2,046 loops=2)

  • Filter: ((userrole <> 255) AND (keytype = ANY ('{0,2}'::integer[])) AND ((CURRENT_DATE - (authtime)::date) <= 30))
  • Rows Removed by Filter: 53348
15. 27.441 61.882 ↑ 1.3 34,783 3

Parallel Hash (cost=1,116.18..1,116.18 rows=43,828 width=30) (actual time=61.881..61.882 rows=34,783 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 7520kB
16. 34.441 34.441 ↑ 1.3 34,783 3

Parallel Seq Scan on appuseraccount ac (cost=0.00..1,116.18 rows=43,828 width=30) (actual time=0.015..34.441 rows=34,783 loops=3)

17. 1.822 3.680 ↓ 1.0 3,583 3

Hash (cost=49.25..49.25 rows=3,565 width=67) (actual time=3.680..3.680 rows=3,583 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 372kB
18. 1.858 1.858 ↓ 1.0 3,583 3

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..49.25 rows=3,565 width=67) (actual time=0.024..1.858 rows=3,583 loops=3)

19. 12.917 23.385 ↑ 1.8 15,852 3

Parallel Hash (cost=464.82..464.82 rows=27,992 width=51) (actual time=23.385..23.385 rows=15,852 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 4704kB
20. 10.468 10.468 ↑ 1.8 15,852 3

Parallel Seq Scan on lockinfo li_1 (cost=0.00..464.82 rows=27,992 width=51) (actual time=0.013..10.468 rows=15,852 loops=3)

21. 25.371 45.834 ↑ 1.2 23,477 3

Parallel Hash (cost=742.59..742.59 rows=29,149 width=45) (actual time=45.834..45.834 rows=23,477 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 6720kB
22. 20.463 20.463 ↑ 1.2 23,477 3

Parallel Seq Scan on houseinfo hi_1 (cost=0.00..742.59 rows=29,149 width=45) (actual time=0.015..20.463 rows=23,477 loops=3)

23. 0.538 4.841 ↓ 1.0 1,563 3

Hash (cost=53.53..53.53 rows=1,559 width=28) (actual time=4.841..4.841 rows=1,563 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
24. 0.803 4.303 ↓ 1.0 1,563 3

Hash Right Join (cost=37.28..53.53 rows=1,559 width=28) (actual time=3.328..4.303 rows=1,563 loops=3)

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
25. 0.257 0.257 ↓ 1.1 1,280 3

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

26. 0.461 3.243 ↑ 1.0 1,546 3

Hash (cost=17.79..17.79 rows=1,559 width=28) (actual time=3.243..3.243 rows=1,546 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
27. 2.782 2.782 ↑ 1.0 1,546 3

Seq Scan on "行政区划代码" xz_1 (cost=0.00..17.79 rows=1,559 width=28) (actual time=0.023..2.782 rows=1,546 loops=3)

28. 1.559 1.969 ↑ 1.0 1,202 3

Hash (cost=14.22..14.22 rows=1,202 width=44) (actual time=1.969..1.969 rows=1,202 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
29. 0.410 0.410 ↑ 1.0 1,202 3

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

30. 518.820 518.820 ↑ 848.4 18 1

CTE Scan on activi (cost=0.00..305.44 rows=15,272 width=32) (actual time=447.653..518.820 rows=18 loops=1)

31. 0.090 7,702.902 ↑ 1,098.0 1 18

Unique (cost=21,548.54..21,551.28 rows=1,098 width=218) (actual time=427.933..427.939 rows=1 loops=18)

32. 0.882 7,702.812 ↑ 33.3 33 18

Sort (cost=21,548.54..21,551.28 rows=1,098 width=218) (actual time=427.930..427.934 rows=33 loops=18)

  • Sort Key: ((opendoor_summary_mrel._0)::timestamp(0) without time zone) DESC NULLS LAST
  • Sort Method: quicksort Memory: 27kB
33. 0.963 7,701.930 ↑ 33.3 33 18

Nested Loop Left Join (cost=13,356.68..21,493.08 rows=1,098 width=218) (actual time=154.415..427.885 rows=33 loops=18)

34. 1.178 7,695.612 ↑ 33.3 33 18

Hash Left Join (cost=13,356.26..20,799.48 rows=1,098 width=280) (actual time=154.399..427.534 rows=33 loops=18)

  • 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: 1
35. 0.786 7,693.992 ↑ 32.6 34 18

Hash Left Join (cost=13,327.01..20,767.29 rows=1,109 width=308) (actual time=154.370..427.444 rows=34 loops=18)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
36. 1.420 7,691.778 ↑ 32.6 34 18

Nested Loop (cost=13,254.00..20,679.11 rows=1,109 width=292) (actual time=154.287..427.321 rows=34 loops=18)

37. 2.044 7,685.892 ↑ 31.7 35 18

Nested Loop (cost=13,253.58..20,183.50 rows=1,111 width=232) (actual time=154.273..426.994 rows=35 loops=18)

38. 3,924.343 7,677.468 ↑ 31.7 35 18

Hash Left Join (cost=13,253.17..19,566.81 rows=1,111 width=1,038) (actual time=154.244..426.526 rows=35 loops=18)

  • 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) AND ((COALESCE(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.account))::text = (activi.bz)::text))
  • Rows Removed by Filter: 232037
39.          

CTE all_key

40. 27.288 344.279 ↓ 1.0 232,072 1

Append (cost=0.00..10,105.81 rows=226,738 width=232) (actual time=0.025..344.279 rows=232,072 loops=1)

41. 260.984 260.984 ↑ 1.0 121,283 1

Seq Scan on lockkeyinfo lk_2 (cost=0.00..7,483.67 rows=122,243 width=238) (actual time=0.024..260.984 rows=121,283 loops=1)

42. 56.007 56.007 ↓ 1.1 110,789 1

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

43. 2,148.481 3,751.002 ↓ 1.0 232,072 18

Hash Left Join (cost=3,044.63..8,168.66 rows=224,476 width=186) (actual time=4.122..208.389 rows=232,072 loops=18)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
44. 1,528.488 1,528.488 ↓ 1.0 232,072 18

CTE Scan on all_key lk (cost=0.00..4,534.76 rows=224,476 width=156) (actual time=0.003..84.916 rows=232,072 loops=18)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL))
45. 29.936 74.033 ↑ 1.0 104,350 1

Hash (cost=1,729.78..1,729.78 rows=105,188 width=30) (actual time=74.033..74.033 rows=104,350 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7345kB
46. 44.097 44.097 ↑ 1.0 104,350 1

Seq Scan on appuseraccount user_info (cost=0.00..1,729.78 rows=105,188 width=30) (actual time=0.007..44.097 rows=104,350 loops=1)

47. 1.008 2.123 ↓ 1.0 3,583 1

Hash (cost=49.25..49.25 rows=3,565 width=56) (actual time=2.122..2.123 rows=3,583 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 350kB
48. 1.115 1.115 ↓ 1.0 3,583 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..49.25 rows=3,565 width=56) (actual time=0.008..1.115 rows=3,583 loops=1)

49. 6.380 6.380 ↑ 1.0 1 638

Index Scan using "pk_lockinfo_1570385706_137 " on lockinfo li (cost=0.41..0.55 rows=1 width=51) (actual time=0.010..0.010 rows=1 loops=638)

  • Index Cond: (((lockid)::text = (lk.lockid)::text) AND (lockid IS NOT NULL))
50. 4.466 4.466 ↑ 1.0 1 638

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo hi (cost=0.42..0.45 rows=1 width=125) (actual time=0.007..0.007 rows=1 loops=638)

  • Index Cond: ((houseid)::text = (li.houseid)::text)
51. 0.301 1.428 ↓ 1.0 1,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
52. 0.466 1.127 ↓ 1.0 1,563 1

Hash Right Join (cost=37.28..53.53 rows=1,559 width=28) (actual time=0.531..1.127 rows=1,563 loops=1)

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

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

54. 0.275 0.520 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
55. 0.245 0.245 ↑ 1.0 1,546 1

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

56. 0.230 0.442 ↑ 1.0 1,202 1

Hash (cost=14.22..14.22 rows=1,202 width=44) (actual time=0.442..0.442 rows=1,202 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
57. 0.212 0.212 ↑ 1.0 1,202 1

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

58. 5.355 5.355 ↑ 1.0 1 595

Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel (cost=0.42..0.63 rows=1 width=38) (actual time=0.009..0.009 rows=1 loops=595)

  • Index Cond: ((lockid = (lk.lockid)::text) AND (userid = (lk.useraccount)::text))