explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I9yL

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 5,043.911 ↑ 15.4 39 1

Sort (cost=22,797.21..22,798.71 rows=600 width=112) (actual time=5,043.907..5,043.911 rows=39 loops=1)

  • Sort Key: group_count."数量" DESC
  • Sort Method: quicksort Memory: 28kB
2. 0.014 5,043.883 ↑ 15.4 39 1

Subquery Scan on group_count (cost=22,750.03..22,769.53 rows=600 width=112) (actual time=5,043.855..5,043.883 rows=39 loops=1)

3. 44.741 5,043.869 ↑ 15.4 39 1

HashAggregate (cost=22,750.03..22,759.03 rows=600 width=112) (actual time=5,043.853..5,043.869 rows=39 loops=1)

  • Hash Key: (CASE WHEN (mod((substr(""*SELECT* 1"".sfzh, 17, 1))::integer, 2) = 1) THEN '男'::text WHEN (mod((substr(""*SELECT* 1"".sfzh, 17, 1))::integer, 2) = 0) THEN '女'::text ELSE '未知'::text END), count(""*SELECT* 1"".sfzh) OVER (?)
  • Hash Key: CASE WHEN (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) < '18'::double precision) THEN '18周岁以下'::text WHEN ((((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) <= '30'::double precision) AND (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) >= '18'::double precision)) THEN '18-30周岁'::text WHEN ((((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) <= '40'::double precision) AND (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) >= '31'::double precision)) THEN '31-40周岁'::text WHEN ((((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) <= '50'::double precision) AND (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) >= '41'::double precision)) THEN '41-50周岁'::text WHEN ((((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) <= '60'::double precision) AND (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) >= '51'::double precision)) THEN '51-60周岁'::text WHEN ((((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) <= '70'::double precision) AND (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) >= '61'::double precision)) THEN '61-70周岁'::text WHEN (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision)) > '70'::double precision) THEN '70岁以上'::text ELSE '未知'::text END, count(""*SELECT* 1"".sfzh) OVER (?)
  • Hash Key: (CASE ""left""(""*SELECT* 1"".sfzh, 2) WHEN '11'::text THEN '北京市'::text WHEN '12'::text THEN '天津市'::text WHEN '13'::text THEN '河北省'::text WHEN '14'::text THEN '山西省'::text WHEN '15'::text THEN '内蒙古自治区'::text WHEN '21'::text THEN '辽宁省'::text WHEN '22'::text THEN '吉林省'::text WHEN '23'::text THEN '黑龙江省'::text WHEN '31'::text THEN '上海市'::text WHEN '32'::text THEN '江苏省'::text WHEN '33'::text THEN '浙江省'::text WHEN '34'::text THEN '安徽省'::text WHEN '35'::text THEN '福建省'::text WHEN '36'::text THEN '江西省'::text WHEN '37'::text THEN '山东省'::text WHEN '41'::text THEN '河南省'::text WHEN '42'::text THEN '湖北省'::text WHEN '43'::text THEN '湖南省'::text WHEN '44'::text THEN '广东省'::text WHEN '45'::text THEN '广西壮族自治区'::text WHEN '46'::text THEN '海南省'::text WHEN '50'::text THEN '重庆市'::text WHEN '51'::text THEN '四川省'::text WHEN '52'::text THEN '贵州省'::text WHEN '53'::text THEN '云南省'::text WHEN '54'::text THEN '西藏自治区'::text WHEN '61'::text THEN '陕西省'::text WHEN '62'::text THEN '甘肃省'::text WHEN '63'::text THEN '青海省'::text WHEN '64'::text THEN '宁夏回族自治区'::text WHEN '65'::text THEN '新疆维吾尔自治区'::text WHEN '71'::text THEN '台湾省'::text WHEN '81'::text THEN '香港特别行政区'::text WHEN '82'::text THEN '澳门特别行政区'::text ELSE '未知'::text END), count(""*SELECT* 1"".sfzh) OVER (?)
4. 41.817 4,999.128 ↓ 193.0 38,598 1

WindowAgg (cost=22,707.53..22,746.53 rows=200 width=136) (actual time=4,978.831..4,999.128 rows=38,598 loops=1)

5. 69.024 4,957.311 ↓ 193.0 38,598 1

HashAggregate (cost=22,707.53..22,736.03 rows=200 width=104) (actual time=4,941.630..4,957.311 rows=38,598 loops=1)

  • Group Key: ""*SELECT* 1"".sfzh, CASE WHEN (mod((substr(""*SELECT* 1"".sfzh, 17, 1))::integer, 2) = 1) THEN '男'::text WHEN (mod((substr(""*SELECT* 1"".sfzh, 17, 1))::integer, 2) = 0) THEN '女'::text ELSE '未知'::text END, (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - ((substr(""*SELECT* 1"".sfzh, 7, 4))::integer)::double precision), CASE ""left""(""*SELECT* 1"".sfzh, 2) WHEN '11'::text THEN '北京市'::text WHEN '12'::text THEN '天津市'::text WHEN '13'::text THEN '河北省'::text WHEN '14'::text THEN '山西省'::text WHEN '15'::text THEN '内蒙古自治区'::text WHEN '21'::text THEN '辽宁省'::text WHEN '22'::text THEN '吉林省'::text WHEN '23'::text THEN '黑龙江省'::text WHEN '31'::text THEN '上海市'::text WHEN '32'::text THEN '江苏省'::text WHEN '33'::text THEN '浙江省'::text WHEN '34'::text THEN '安徽省'::text WHEN '35'::text THEN '福建省'::text WHEN '36'::text THEN '江西省'::text WHEN '37'::text THEN '山东省'::text WHEN '41'::text THEN '河南省'::text WHEN '42'::text THEN '湖北省'::text WHEN '43'::text THEN '湖南省'::text WHEN '44'::text THEN '广东省'::text WHEN '45'::text THEN '广西壮族自治区'::text WHEN '46'::text THEN '海南省'::text WHEN '50'::text THEN '重庆市'::text WHEN '51'::text THEN '四川省'::text WHEN '52'::text THEN '贵州省'::text WHEN '53'::text THEN '云南省'::text WHEN '54'::text THEN '西藏自治区'::text WHEN '61'::text THEN '陕西省'::text WHEN '62'::text THEN '甘肃省'::text WHEN '63'::text THEN '青海省'::text WHEN '64'::text THEN '宁夏回族自治区'::text WHEN '65'::text THEN '新疆维吾尔自治区'::text WHEN '71'::text THEN '台湾省'::text WHEN '81'::text THEN '香港特别行政区'::text WHEN '82'::text THEN '澳门特别行政区'::text ELSE '未知'::text END
6. 152.628 4,888.287 ↓ 2.3 42,038 1

Result (cost=8,399.12..22,525.99 rows=18,154 width=104) (actual time=279.477..4,888.287 rows=42,038 loops=1)

7. 9.967 4,735.659 ↓ 2.3 42,038 1

Append (cost=8,399.12..19,939.04 rows=18,154 width=32) (actual time=279.462..4,735.659 rows=42,038 loops=1)

8. 12.277 4,241.048 ↓ 2.3 38,169 1

Subquery Scan on *SELECT* 1 (cost=8,399.12..17,936.56 rows=16,491 width=32) (actual time=279.461..4,241.048 rows=38,169 loops=1)

9. 1,786.970 4,228.771 ↓ 2.3 38,169 1

Hash Left Join (cost=8,399.12..17,771.65 rows=16,491 width=308) (actual time=279.461..4,228.771 rows=38,169 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 1,840.602 2,439.422 ↓ 2.3 38,169 1

Hash Left Join (cost=8,321.64..13,180.64 rows=16,491 width=48) (actual time=276.594..2,439.422 rows=38,169 loops=1)

  • Hash Cond: (((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text = (bk.bluekeyaccount)::text) AND ((lockkeyinfo.lockid)::text = (bk.lockid)::text))
  • Filter: ((COALESCE(bk.certid, ac.account) IS NOT NULL) AND (dev.""身份证号校验""((CASE WHEN (substr((COALESCE(bk.certid, ac.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, ac.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, ac.userid) END)::text) IS NOT NULL))
  • Rows Removed by Filter: 1281
11. 50.753 594.799 ↓ 2.4 39,450 1

Hash Left Join (cost=8,201.04..12,972.59 rows=16,657 width=84) (actual time=272.002..594.799 rows=39,450 loops=1)

  • Hash Cond: ((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text = (ac.account)::text)
12. 77.688 429.595 ↓ 2.4 39,450 1

Hash Join (cost=3,971.32..8,699.15 rows=16,657 width=55) (actual time=157.379..429.595 rows=39,450 loops=1)

  • Hash Cond: ((lockkeyinfo.lockid)::text = (li.lockid)::text)
13. 194.608 194.608 ↓ 2.7 76,238 1

Seq Scan on lockkeyinfo (cost=0.00..4,453.90 rows=28,629 width=43) (actual time=0.018..194.608 rows=76,238 loops=1)

  • Filter: ((userrole <> 255) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 75596
14. 12.634 157.299 ↑ 1.0 35,792 1

Hash (cost=3,506.72..3,506.72 rows=37,168 width=31) (actual time=157.298..157.299 rows=35,792 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2784kB
15. 42.367 144.665 ↑ 1.0 35,792 1

Hash Join (cost=2,449.61..3,506.72 rows=37,168 width=31) (actual time=87.008..144.665 rows=35,792 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
16. 15.347 15.347 ↑ 1.0 63,746 1

Seq Scan on lockinfo li (cost=0.00..889.41 rows=63,881 width=52) (actual time=0.006..15.347 rows=63,746 loops=1)

17. 21.462 86.951 ↑ 1.0 52,099 1

Hash (cost=1,768.49..1,768.49 rows=54,490 width=46) (actual time=86.950..86.951 rows=52,099 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4481kB
18. 65.489 65.489 ↑ 1.0 52,099 1

Seq Scan on houseinfo hi (cost=0.00..1,768.49 rows=54,490 width=46) (actual time=0.007..65.489 rows=52,099 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 41572
19. 49.313 114.451 ↓ 1.0 144,828 1

Hash (cost=2,422.82..2,422.82 rows=144,552 width=29) (actual time=114.451..114.451 rows=144,828 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10822kB
20. 65.138 65.138 ↓ 1.0 144,828 1

Seq Scan on appuseraccount ac (cost=0.00..2,422.82 rows=144,552 width=29) (actual time=0.011..65.138 rows=144,828 loops=1)

21. 2.256 4.021 ↓ 1.0 4,204 1

Hash (cost=57.72..57.72 rows=4,192 width=56) (actual time=4.021..4.021 rows=4,204 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 437kB
22. 1.765 1.765 ↓ 1.0 4,204 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..57.72 rows=4,192 width=56) (actual time=0.010..1.765 rows=4,204 loops=1)

23. 0.493 2.379 ↓ 1.0 1,639 1

Hash (cost=57.10..57.10 rows=1,630 width=12) (actual time=2.378..2.379 rows=1,639 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
24. 0.739 1.886 ↓ 1.0 1,639 1

Hash Right Join (cost=38.98..57.10 rows=1,630 width=12) (actual time=0.924..1.886 rows=1,639 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
25. 0.236 0.236 ↑ 1.0 1,332 1

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

26. 0.496 0.911 ↓ 1.0 1,632 1

Hash (cost=18.60..18.60 rows=1,630 width=28) (actual time=0.911..0.911 rows=1,632 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
27. 0.415 0.415 ↓ 1.0 1,632 1

Seq Scan on "行政区划代码" xz (cost=0.00..18.60 rows=1,630 width=28) (actual time=0.010..0.415 rows=1,632 loops=1)

28. 1.789 484.644 ↓ 2.3 3,869 1

Subquery Scan on *SELECT* 2 (cost=821.73..1,911.71 rows=1,663 width=32) (actual time=30.783..484.644 rows=3,869 loops=1)

29. 414.070 482.855 ↓ 2.3 3,869 1

Hash Left Join (cost=821.73..1,895.08 rows=1,663 width=308) (actual time=30.782..482.855 rows=3,869 loops=1)

  • Hash Cond: (((COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text = (bk_1.bluekeyaccount)::text) AND ((lockkeyinfo_1.lockid)::text = (bk_1.lockid)::text))
  • Filter: ((COALESCE(bk_1.certid, ac_1.account) IS NOT NULL) AND (dev.""身份证号校验""((CASE WHEN (substr((COALESCE(bk_1.certid, ac_1.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac_1.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac_1.userid) END)::text) IS NOT NULL))
  • Rows Removed by Filter: 211
30. 4.403 68.675 ↓ 2.4 4,080 1

Hash Left Join (cost=818.46..1,450.60 rows=1,680 width=74) (actual time=29.696..68.675 rows=4,080 loops=1)

  • Hash Cond: ((COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text = (ac_1.account)::text)
31. 3.258 51.277 ↓ 2.4 4,080 1

Hash Left Join (cost=392.87..1,020.60 rows=1,680 width=44) (actual time=16.679..51.277 rows=4,080 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
32. 5.787 45.663 ↓ 2.4 4,080 1

Hash Join (cost=315.39..927.38 rows=1,680 width=56) (actual time=14.311..45.663 rows=4,080 loops=1)

  • Hash Cond: ((lockkeyinfo_1.lockid)::text = (li_1.lockid)::text)
33. 25.615 25.615 ↓ 1.4 6,091 1

Seq Scan on lockkeyinfo lockkeyinfo_1 (cost=0.00..578.85 rows=4,356 width=44) (actual time=0.018..25.615 rows=6,091 loops=1)

  • Filter: ((userrole <> 255) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 13149
34. 1.041 14.261 ↓ 1.2 2,692 1

Hash (cost=286.87..286.87 rows=2,282 width=31) (actual time=14.261..14.261 rows=2,692 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 202kB
35. 1.393 13.220 ↓ 1.2 2,692 1

Hash Join (cost=183.79..286.87 rows=2,282 width=31) (actual time=6.723..13.220 rows=2,692 loops=1)

  • Hash Cond: ((hi_1.customid)::text = (ci.customid)::text)
36. 3.661 11.641 ↓ 1.0 2,702 1

Hash Join (cost=177.39..273.29 rows=2,695 width=63) (actual time=6.531..11.641 rows=2,702 loops=1)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
37. 1.467 1.467 ↑ 1.0 5,888 1

Seq Scan on lockinfo li_1 (cost=0.00..80.36 rows=5,916 width=45) (actual time=0.005..1.467 rows=5,888 loops=1)

38. 1.477 6.513 ↓ 1.0 3,592 1

Hash (cost=133.14..133.14 rows=3,540 width=77) (actual time=6.512..6.513 rows=3,592 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 419kB
39. 5.036 5.036 ↓ 1.0 3,592 1

Seq Scan on houseinfo hi_1 (cost=0.00..133.14 rows=3,540 width=77) (actual time=0.007..5.036 rows=3,592 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 4270
40. 0.094 0.186 ↓ 1.2 295 1

Hash (cost=3.29..3.29 rows=249 width=33) (actual time=0.186..0.186 rows=295 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
41. 0.092 0.092 ↓ 1.2 295 1

Seq Scan on custominfo ci (cost=0.00..3.29 rows=249 width=33) (actual time=0.008..0.092 rows=295 loops=1)

42. 0.476 2.356 ↓ 1.0 1,639 1

Hash (cost=57.10..57.10 rows=1,630 width=12) (actual time=2.356..2.356 rows=1,639 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
43. 0.725 1.880 ↓ 1.0 1,639 1

Hash Right Join (cost=38.98..57.10 rows=1,630 width=12) (actual time=0.945..1.880 rows=1,639 loops=1)

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
44. 0.224 0.224 ↑ 1.0 1,332 1

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

45. 0.505 0.931 ↓ 1.0 1,632 1

Hash (cost=18.60..18.60 rows=1,630 width=28) (actual time=0.931..0.931 rows=1,632 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
46. 0.426 0.426 ↓ 1.0 1,632 1

Seq Scan on "行政区划代码" xz_1 (cost=0.00..18.60 rows=1,630 width=28) (actual time=0.009..0.426 rows=1,632 loops=1)

47. 5.293 12.995 ↑ 1.0 15,826 1

Hash (cost=227.04..227.04 rows=15,884 width=30) (actual time=12.995..12.995 rows=15,826 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1006kB
48. 7.702 7.702 ↑ 1.0 15,826 1

Seq Scan on appuseraccount ac_1 (cost=0.00..227.04 rows=15,884 width=30) (actual time=0.010..7.702 rows=15,826 loops=1)

49. 0.050 0.110 ↓ 1.0 112 1

Hash (cost=1.61..1.61 rows=111 width=57) (actual time=0.110..0.110 rows=112 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
50. 0.060 0.060 ↓ 1.0 112 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..1.61 rows=111 width=57) (actual time=0.008..0.060 rows=112 loops=1)