explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tAFT

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 5,827.279 ↑ 138.9 39 1

Sort (cost=24,001.95..24,015.50 rows=5,418 width=112) (actual time=5,827.275..5,827.279 rows=39 loops=1)

  • Sort Key: group_count."数量" DESC
  • Sort Method: quicksort Memory: 28kB
2. 0.012 5,827.250 ↑ 138.9 39 1

Subquery Scan on group_count (cost=23,245.97..23,665.94 rows=5,418 width=112) (actual time=5,827.204..5,827.250 rows=39 loops=1)

3. 65.349 5,827.238 ↑ 138.9 39 1

HashAggregate (cost=23,245.97..23,571.12 rows=5,418 width=112) (actual time=5,827.202..5,827.238 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: sf.""省份"", count(""*SELECT* 1"".sfzh) OVER (?)
4. 51.543 5,761.889 ↓ 2.1 38,612 1

WindowAgg (cost=20,987.84..22,929.83 rows=18,065 width=136) (actual time=5,742.347..5,761.889 rows=38,612 loops=1)

5. 77.230 5,710.346 ↓ 2.1 38,612 1

HashAggregate (cost=20,987.84..21,981.42 rows=18,065 width=104) (actual time=5,687.860..5,710.346 rows=38,612 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), sf.""省份"
6. 187.852 5,633.116 ↓ 2.3 42,059 1

Hash Join (cost=8,426.79..20,807.19 rows=18,065 width=104) (actual time=316.130..5,633.116 rows=42,059 loops=1)

  • Hash Cond: (substr(""*SELECT* 1"".sfzh, 1, 2) = sf.""代码"")
7. 11.588 5,445.202 ↓ 2.3 42,059 1

Append (cost=8,408.91..19,926.42 rows=18,065 width=32) (actual time=316.051..5,445.202 rows=42,059 loops=1)

8. 13.410 4,786.514 ↓ 2.3 38,188 1

Subquery Scan on *SELECT* 1 (cost=8,408.91..17,922.56 rows=16,400 width=32) (actual time=316.050..4,786.514 rows=38,188 loops=1)

9. 2,053.820 4,773.104 ↓ 2.3 38,188 1

Hash Left Join (cost=8,408.91..17,758.56 rows=16,400 width=308) (actual time=316.049..4,773.104 rows=38,188 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 2,064.761 2,716.934 ↓ 2.3 38,188 1

Hash Left Join (cost=8,331.44..13,192.45 rows=16,400 width=48) (actual time=313.264..2,716.934 rows=38,188 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 (""身份证号校验""((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: 1279
11. 44.766 648.589 ↓ 2.4 39,467 1

Hash Left Join (cost=8,210.84..12,984.88 rows=16,565 width=84) (actual time=309.061..648.589 rows=39,467 loops=1)

  • Hash Cond: ((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text = (ac.account)::text)
12. 69.549 468.122 ↓ 2.4 39,467 1

Hash Join (cost=3,981.12..8,711.68 rows=16,565 width=55) (actual time=173.178..468.122 rows=39,467 loops=1)

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

Seq Scan on lockkeyinfo (cost=0.00..4,458.15 rows=28,471 width=43) (actual time=0.016..225.477 rows=76,362 loops=1)

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

Hash (cost=3,515.40..3,515.40 rows=37,257 width=31) (actual time=173.096..173.096 rows=35,792 loops=1)

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

Hash Join (cost=2,455.76..3,515.40 rows=37,257 width=31) (actual time=92.049..158.323 rows=35,792 loops=1)

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

Seq Scan on lockinfo li (cost=0.00..891.54 rows=64,034 width=52) (actual time=0.006..17.532 rows=63,848 loops=1)

17. 21.574 91.954 ↑ 1.0 52,099 1

Hash (cost=1,772.93..1,772.93 rows=54,627 width=46) (actual time=91.954..91.954 rows=52,099 loops=1)

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

Seq Scan on houseinfo hi (cost=0.00..1,772.93 rows=54,627 width=46) (actual time=0.009..70.380 rows=52,099 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 41826
19. 58.047 135.701 ↓ 1.0 144,953 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10829kB
20. 77.654 77.654 ↓ 1.0 144,953 1

Seq Scan on appuseraccount ac (cost=0.00..2,422.82 rows=144,552 width=29) (actual time=0.009..77.654 rows=144,953 loops=1)

21. 1.731 3.584 ↓ 1.0 4,204 1

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

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

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

23. 0.511 2.350 ↓ 1.0 1,639 1

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

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

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

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

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

26. 0.462 0.877 ↓ 1.0 1,632 1

Hash (cost=18.60..18.60 rows=1,630 width=28) (actual time=0.877..0.877 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.011..0.415 rows=1,632 loops=1)

28. 1.555 647.100 ↓ 2.3 3,871 1

Subquery Scan on *SELECT* 2 (cost=822.35..1,913.54 rows=1,665 width=32) (actual time=31.164..647.100 rows=3,871 loops=1)

29. 568.393 645.545 ↓ 2.3 3,871 1

Hash Left Join (cost=822.35..1,896.89 rows=1,665 width=308) (actual time=31.163..645.545 rows=3,871 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 (""身份证号校验""((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: 212
30. 4.850 77.045 ↓ 2.4 4,083 1

Hash Left Join (cost=819.08..1,451.88 rows=1,682 width=74) (actual time=30.038..77.045 rows=4,083 loops=1)

  • Hash Cond: ((COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text = (ac_1.account)::text)
31. 6.864 59.192 ↓ 2.4 4,083 1

Hash Left Join (cost=392.87..1,021.26 rows=1,682 width=44) (actual time=17.010..59.192 rows=4,083 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
32. 5.883 49.908 ↓ 2.4 4,083 1

Hash Join (cost=315.39..928.01 rows=1,682 width=56) (actual time=14.574..49.908 rows=4,083 loops=1)

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

Seq Scan on lockkeyinfo lockkeyinfo_1 (cost=0.00..579.45 rows=4,361 width=44) (actual time=0.020..29.512 rows=6,098 loops=1)

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

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

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

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

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

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

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

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

38. 1.466 6.421 ↓ 1.0 3,592 1

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

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

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

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 4271
40. 0.087 0.244 ↓ 1.2 295 1

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

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

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

42. 0.521 2.420 ↓ 1.0 1,639 1

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

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

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

  • Hash Cond: (ys_1."行政区划_id" = xz_1.id)
44. 0.230 0.230 ↑ 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.230 rows=1,332 loops=1)

45. 0.476 0.943 ↓ 1.0 1,632 1

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

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

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

47. 5.138 13.003 ↑ 1.0 15,837 1

Hash (cost=227.37..227.37 rows=15,907 width=30) (actual time=13.003..13.003 rows=15,837 loops=1)

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

Seq Scan on appuseraccount ac_1 (cost=0.00..227.37 rows=15,907 width=30) (actual time=0.011..7.865 rows=15,837 loops=1)

49. 0.048 0.107 ↓ 1.0 113 1

Hash (cost=1.61..1.61 rows=111 width=57) (actual time=0.107..0.107 rows=113 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
50. 0.059 0.059 ↓ 1.0 113 1

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

51. 0.013 0.062 ↑ 22.1 34 1

Hash (cost=8.50..8.50 rows=750 width=64) (actual time=0.061..0.062 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
52. 0.049 0.049 ↑ 22.1 34 1

Seq Scan on "全国身份证_省份" sf (cost=0.00..8.50 rows=750 width=64) (actual time=0.040..0.049 rows=34 loops=1)