explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tmwn

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 4,579.508 ↑ 52.0 1 1

GroupAggregate (cost=81,024.57..81,026.26 rows=52 width=850) (actual time=4,579.507..4,579.508 rows=1 loops=1)

  • Group Key: leave_people.bz, leave_people.expireddate, hlk.detailaddr, hlk.housenumber
2.          

CTE xzqh_id

3. 0.020 0.773 ↓ 2.5 20 1

Nested Loop (cost=17.85..38.08 rows=8 width=28) (actual time=0.699..0.773 rows=20 loops=1)

4. 0.209 0.693 ↓ 2.5 20 1

Hash Join (cost=17.57..35.70 rows=8 width=16) (actual time=0.679..0.693 rows=20 loops=1)

  • Hash Cond: (ys."管理单位代码_id" = gl.id)
5. 0.169 0.169 ↑ 1.0 1,332 1

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

6. 0.005 0.315 ↑ 1.0 7 1

Hash (cost=17.49..17.49 rows=7 width=16) (actual time=0.314..0.315 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.310 0.310 ↑ 1.0 7 1

Seq Scan on "管理单位代码" gl (cost=0.00..17.49 rows=7 width=16) (actual time=0.303..0.310 rows=7 loops=1)

  • Filter: (("代码" = ANY ('{330503500000}'::text[])) OR ("上级单位代码" = ANY ('{330503500000}'::text[])))
  • Rows Removed by Filter: 1216
8. 0.060 0.060 ↑ 1.0 1 20

Index Scan using "行政区划代码_pkey" on "行政区划代码" xz (cost=0.28..0.30 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: (id = ys."行政区划_id")
9.          

CTE people

10. 381.528 3,220.271 ↓ 14.2 150,944 1

Hash Left Join (cost=34,886.88..63,428.86 rows=10,605 width=952) (actual time=1,363.753..3,220.271 rows=150,944 loops=1)

  • Hash Cond: ((li.lockid)::text = (ls.lockid)::text)
11.          

CTE all_key

12. 19.499 295.887 ↑ 1.0 167,821 1

Append (cost=0.00..11,877.55 rows=173,463 width=283) (actual time=0.031..295.887 rows=167,821 loops=1)

13. 169.818 169.818 ↑ 1.0 74,624 1

Seq Scan on lockkeyinfo lk (cost=0.00..5,315.18 rows=76,180 width=290) (actual time=0.030..169.818 rows=74,624 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 51879
14. 106.570 106.570 ↑ 1.0 93,197 1

Seq Scan on keyhistoryinfo khi (cost=0.00..3,960.42 rows=97,283 width=278) (actual time=0.014..106.570 rows=93,197 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 61502
15. 101.178 2,760.615 ↓ 15.0 150,944 1

Hash Left Join (cost=20,121.66..48,107.01 rows=10,047 width=922) (actual time=1,285.561..2,760.615 rows=150,944 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz_1."代码")
16. 158.955 2,656.292 ↓ 15.0 150,944 1

Hash Join (cost=20,010.95..47,858.75 rows=10,047 width=832) (actual time=1,282.402..2,656.292 rows=150,944 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
17. 121.481 2,406.947 ↓ 16.1 162,460 1

Hash Join (cost=17,771.48..45,592.86 rows=10,062 width=511) (actual time=1,191.866..2,406.947 rows=162,460 loops=1)

  • Hash Cond: (("*SELECT* 1".lockid)::text = (li.lockid)::text)
18. 21.508 2,249.563 ↓ 16.2 162,624 1

Append (cost=16,353.90..44,148.87 rows=10,062 width=538) (actual time=1,155.906..2,249.563 rows=162,624 loops=1)

19. 33.641 1,418.722 ↓ 15.3 153,404 1

Subquery Scan on *SELECT* 1 (cost=16,353.90..21,854.42 rows=10,012 width=538) (actual time=1,155.905..1,418.722 rows=153,404 loops=1)

20. 156.041 1,385.081 ↓ 15.3 153,404 1

Hash Join (cost=16,353.90..21,754.30 rows=10,012 width=538) (actual time=1,155.903..1,385.081 rows=153,404 loops=1)

  • Hash Cond: ((ac_1.account)::text = (all_key.useraccount)::text)
21. 188.876 655.976 ↓ 1.0 121,136 1

HashAggregate (cost=11,583.45..15,116.98 rows=117,784 width=274) (actual time=582.824..655.976 rows=121,136 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_1.certid, ac_1.userid, ac_1.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_1.certid, ac_1.userid, ac_1.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_1.certid, ac_1.userid, ac_1.account)) END, ac_1.account, 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, max((COALESCE(ac_1.username, bk_1.username))::text), max((COALESCE(ac_1.headpicture, bk_1.certfrontpic))::text), bk_1.lockid, bk_1.bluekeyaccount, max((ac_1.authstate)::text)
22. 260.331 467.100 ↓ 1.0 121,136 1

HashAggregate (cost=5,694.26..9,227.77 rows=117,784 width=274) (actual time=315.415..467.100 rows=121,136 loops=1)

  • Group Key: COALESCE(bk_1.certid, ac_1.userid, ac_1.account), bk_1.bluekeyaccount, ac_1.account, COALESCE(bk_1.certid, ac_1.userid)
23. 96.429 206.769 ↓ 1.0 121,136 1

Hash Full Join (cost=3,481.66..3,633.03 rows=117,784 width=356) (actual time=110.013..206.769 rows=121,136 loops=1)

  • Hash Cond: ((bk_1.certid)::text = (ac_1.userid)::text)
  • Filter: (CASE WHEN (substr((COALESCE(bk_1.certid, ac_1.userid, ac_1.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_1.certid, ac_1.userid, ac_1.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_1.certid, ac_1.userid, ac_1.account) END IS NOT NULL)
24. 0.807 0.807 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.009..0.807 rows=3,876 loops=1)

25. 52.006 109.533 ↑ 1.0 118,352 1

Hash (cost=2,001.96..2,001.96 rows=118,376 width=124) (actual time=109.533..109.533 rows=118,352 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18644kB
26. 57.527 57.527 ↑ 1.0 118,352 1

Seq Scan on appuseraccount ac_1 (cost=0.00..2,001.96 rows=118,376 width=124) (actual time=0.018..57.527 rows=118,352 loops=1)

27. 85.800 573.064 ↓ 9,871.8 167,821 1

Hash (cost=4,770.23..4,770.23 rows=17 width=506) (actual time=573.064..573.064 rows=167,821 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 34111kB
28. 487.264 487.264 ↓ 9,871.8 167,821 1

CTE Scan on all_key (cost=0.00..4,770.23 rows=17 width=506) (actual time=0.038..487.264 rows=167,821 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (((ly = 'historykey'::text) AND (stoptime IS NOT NULL)) OR (ly = 'lockkey'::text)))
29. 2.124 809.333 ↓ 184.4 9,220 1

Subquery Scan on *SELECT* 2 (cost=16,353.94..22,244.14 rows=50 width=538) (actual time=733.796..809.333 rows=9,220 loops=1)

30. 18.310 807.209 ↓ 184.4 9,220 1

Hash Join (cost=16,353.94..22,243.64 rows=50 width=538) (actual time=733.795..807.209 rows=9,220 loops=1)

  • Hash Cond: (((bk_2.bluekeyaccount)::text = (all_key_1.useraccount)::text) AND ((bk_2.lockid)::text = (all_key_1.lockid)::text))
31. 166.840 620.864 ↓ 1.0 121,136 1

HashAggregate (cost=11,583.45..15,116.98 rows=117,784 width=274) (actual time=565.713..620.864 rows=121,136 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk_2.certid, ac_2.userid, ac_2.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_2.certid, ac_2.userid, ac_2.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_2.certid, ac_2.userid, ac_2.account)) END, ac_2.account, CASE WHEN (substr(((COALESCE(bk_2.certid, ac_2.userid)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk_2.certid, ac_2.userid)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk_2.certid, ac_2.userid)) END, max((COALESCE(ac_2.username, bk_2.username))::text), max((COALESCE(ac_2.headpicture, bk_2.certfrontpic))::text), bk_2.lockid, bk_2.bluekeyaccount, max((ac_2.authstate)::text)
32. 255.239 454.024 ↓ 1.0 121,136 1

HashAggregate (cost=5,694.26..9,227.77 rows=117,784 width=274) (actual time=302.598..454.024 rows=121,136 loops=1)

  • Group Key: COALESCE(bk_2.certid, ac_2.userid, ac_2.account), bk_2.bluekeyaccount, ac_2.account, COALESCE(bk_2.certid, ac_2.userid)
33. 95.546 198.785 ↓ 1.0 121,136 1

Hash Full Join (cost=3,481.66..3,633.03 rows=117,784 width=356) (actual time=102.841..198.785 rows=121,136 loops=1)

  • Hash Cond: ((bk_2.certid)::text = (ac_2.userid)::text)
  • Filter: (CASE WHEN (substr((COALESCE(bk_2.certid, ac_2.userid, ac_2.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk_2.certid, ac_2.userid, ac_2.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk_2.certid, ac_2.userid, ac_2.account) END IS NOT NULL)
34. 0.814 0.814 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk_2 (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.008..0.814 rows=3,876 loops=1)

35. 48.550 102.425 ↑ 1.0 118,352 1

Hash (cost=2,001.96..2,001.96 rows=118,376 width=124) (actual time=102.425..102.425 rows=118,352 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18644kB
36. 53.875 53.875 ↑ 1.0 118,352 1

Seq Scan on appuseraccount ac_2 (cost=0.00..2,001.96 rows=118,376 width=124) (actual time=0.022..53.875 rows=118,352 loops=1)

37. 92.196 168.035 ↓ 9,871.8 167,821 1

Hash (cost=4,770.23..4,770.23 rows=17 width=506) (actual time=168.035..168.035 rows=167,821 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 34111kB
38. 75.839 75.839 ↓ 9,871.8 167,821 1

CTE Scan on all_key all_key_1 (cost=0.00..4,770.23 rows=17 width=506) (actual time=0.007..75.839 rows=167,821 loops=1)

  • Filter: ((keytype = ANY ('{0,2}'::integer[])) AND (((ly = 'historykey'::text) AND (stoptime IS NOT NULL)) OR (ly = 'lockkey'::text)))
39. 14.479 35.903 ↑ 1.0 52,786 1

Hash (cost=740.59..740.59 rows=54,159 width=71) (actual time=35.903..35.903 rows=52,786 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 5901kB
40. 21.424 21.424 ↑ 1.0 52,786 1

Seq Scan on lockinfo li (cost=0.00..740.59 rows=54,159 width=71) (actual time=0.011..21.424 rows=52,786 loops=1)

41. 42.607 90.390 ↑ 1.0 77,829 1

Hash (cost=1,266.49..1,266.49 rows=77,839 width=353) (actual time=90.390..90.390 rows=77,829 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 24532kB
42. 47.783 47.783 ↑ 1.0 77,829 1

Seq Scan on houseinfo hi (cost=0.00..1,266.49 rows=77,839 width=353) (actual time=0.020..47.783 rows=77,829 loops=1)

43. 0.541 3.145 ↓ 1.0 1,639 1

Hash (cost=90.33..90.33 rows=1,630 width=102) (actual time=3.145..3.145 rows=1,639 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 215kB
44. 0.568 2.604 ↓ 1.0 1,639 1

Hash Right Join (cost=68.69..90.33 rows=1,630 width=102) (actual time=1.387..2.604 rows=1,639 loops=1)

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

Hash Left Join (cost=29.72..47.85 rows=1,332 width=90) (actual time=0.647..1.304 rows=1,332 loops=1)

  • Hash Cond: (ys_1."管理单位代码_id" = gl_1.id)
46. 0.173 0.173 ↑ 1.0 1,332 1

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

47. 0.341 0.632 ↑ 1.0 1,223 1

Hash (cost=14.43..14.43 rows=1,223 width=90) (actual time=0.632..0.632 rows=1,223 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 163kB
48. 0.291 0.291 ↑ 1.0 1,223 1

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

49. 0.380 0.732 ↓ 1.0 1,632 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 141kB
50. 0.352 0.352 ↓ 1.0 1,632 1

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

51. 13.698 78.128 ↑ 1.0 57,170 1

Hash (cost=2,173.05..2,173.05 rows=57,170 width=23) (actual time=78.128..78.128 rows=57,170 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3637kB
52. 9.339 64.430 ↑ 1.0 57,170 1

Subquery Scan on ls (cost=1,029.65..2,173.05 rows=57,170 width=23) (actual time=38.268..64.430 rows=57,170 loops=1)

53. 43.351 55.091 ↑ 1.0 57,170 1

HashAggregate (cost=1,029.65..1,601.35 rows=57,170 width=23) (actual time=38.267..55.091 rows=57,170 loops=1)

  • Group Key: lockstateinfo.lockid
54. 11.740 11.740 ↑ 1.0 57,170 1

Seq Scan on lockstateinfo (cost=0.00..743.80 rows=57,170 width=23) (actual time=0.008..11.740 rows=57,170 loops=1)

55.          

CTE leave_people

56. 1.052 2,429.344 ↑ 1.0 1 1

HashAggregate (cost=413.96..417.96 rows=1 width=64) (actual time=2,429.211..2,429.344 rows=1 loops=1)

  • Group Key: people.bz
  • Filter: (max((COALESCE(people.stoptime, people.expireddate))::text) ~~ (('2020-01-27'::date)::text || '%'::text))
  • Rows Removed by Filter: 304
57.          

Initplan (for HashAggregate)

58. 0.005 0.016 ↑ 1.0 1 1

Aggregate (cost=1.50..1.51 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)

59. 0.011 0.011 ↑ 100.0 1 1

Function Scan on unnest t (cost=0.00..1.00 rows=100 width=32) (actual time=0.011..0.011 rows=1 loops=1)

60. 2,427.485 2,428.276 ↑ 8.0 683 1

CTE Scan on people (cost=0.18..371.36 rows=5,479 width=122) (actual time=6.417..2,428.276 rows=683 loops=1)

  • Filter: ((authtime IS NOT NULL) AND (expireddate IS NOT NULL) AND (bz IS NOT NULL) AND ((hashed SubPlan 4) OR ((xzqhdm)::text ~~ ANY ($5))))
  • Rows Removed by Filter: 150261
61.          

SubPlan (for CTE Scan)

62. 0.791 0.791 ↓ 2.5 20 1

CTE Scan on xzqh_id (cost=0.00..0.16 rows=8 width=32) (actual time=0.700..0.791 rows=20 loops=1)

63. 0.013 4,579.496 ↑ 52.0 1 1

Sort (cost=17,033.61..17,033.74 rows=52 width=766) (actual time=4,579.496..4,579.496 rows=1 loops=1)

  • Sort Key: leave_people.bz, leave_people.expireddate, hlk.detailaddr, hlk.housenumber
  • Sort Method: quicksort Memory: 25kB
64. 15.716 4,579.483 ↑ 52.0 1 1

Hash Right Join (cost=11,847.26..17,032.13 rows=52 width=766) (actual time=4,524.854..4,579.483 rows=1 loops=1)

  • Hash Cond: (((CASE WHEN (substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk.certid, ac.userid, ac.account)) END))::text = (leave_people.bz)::text)
65. 163.564 567.089 ↓ 1.0 121,136 1

HashAggregate (cost=11,582.08..15,133.36 rows=118,376 width=274) (actual time=514.601..567.089 rows=121,136 loops=1)

  • Group Key: CASE WHEN (substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 18, 1) = 'x'::text) THEN ((substr(((COALESCE(bk.certid, ac.userid, ac.account)))::text, 1, 17) || 'X'::text))::character varying ELSE (COALESCE(bk.certid, ac.userid, ac.account)) END, ac.account, 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, max((COALESCE(ac.username, bk.username))::text), max((COALESCE(ac.headpicture, bk.certfrontpic))::text), bk.lockid, bk.bluekeyaccount, max((ac.authstate)::text)
66. 246.041 403.525 ↓ 1.0 121,136 1

HashAggregate (cost=5,663.27..9,214.56 rows=118,376 width=274) (actual time=253.337..403.525 rows=121,136 loops=1)

  • Group Key: COALESCE(bk.certid, ac.userid, ac.account), bk.bluekeyaccount, ac.account, COALESCE(bk.certid, ac.userid)
67. 46.412 157.484 ↓ 1.0 121,136 1

Hash Full Join (cost=3,481.66..3,591.69 rows=118,376 width=356) (actual time=110.383..157.484 rows=121,136 loops=1)

  • Hash Cond: ((bk.certid)::text = (ac.userid)::text)
68. 0.795 0.795 ↑ 1.0 3,876 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..54.16 rows=3,876 width=168) (actual time=0.008..0.795 rows=3,876 loops=1)

69. 53.078 110.277 ↑ 1.0 118,352 1

Hash (cost=2,001.96..2,001.96 rows=118,376 width=124) (actual time=110.277..110.277 rows=118,352 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18644kB
70. 57.199 57.199 ↑ 1.0 118,352 1

Seq Scan on appuseraccount ac (cost=0.00..2,001.96 rows=118,376 width=124) (actual time=0.013..57.199 rows=118,352 loops=1)

71. 0.004 3,996.678 ↑ 1.0 1 1

Hash (cost=265.17..265.17 rows=1 width=658) (actual time=3,996.678..3,996.678 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 37.825 3,996.674 ↑ 1.0 1 1

Hash Join (cost=0.04..265.17 rows=1 width=658) (actual time=3,837.285..3,996.674 rows=1 loops=1)

  • Hash Cond: (((hlk.bz)::text = (leave_people.bz)::text) AND ((COALESCE(hlk.stoptime, hlk.expireddate))::text = leave_people.expireddate))
73. 1,529.497 1,529.497 ↓ 42.6 150,664 1

CTE Scan on people hlk (cost=0.00..238.61 rows=3,535 width=716) (actual time=1,363.763..1,529.497 rows=150,664 loops=1)

  • Filter: ((expireddate)::text > (authtime)::text)
  • Rows Removed by Filter: 280
74. 0.004 2,429.352 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=2,429.352..2,429.352 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 2,429.348 2,429.348 ↑ 1.0 1 1

CTE Scan on leave_people (cost=0.00..0.02 rows=1 width=64) (actual time=2,429.213..2,429.348 rows=1 loops=1)

Planning time : 3.752 ms
Execution time : 4,590.615 ms