explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AuSX

Settings
# exclusive inclusive rows x rows loops node
1. 4.157 2,547.840 ↑ 3.4 4,732 1

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

2. 13.438 2,543.683 ↑ 3.4 4,733 1

Sort (cost=91,679.35..91,719.50 rows=16,060 width=895) (actual time=2,542.863..2,543.683 rows=4,733 loops=1)

  • Sort Key: (COALESCE(user_info.headpicture, bk.certfrontpic, bk.certbackpic)), (COALESCE(user_info.username, bk.username)), (COALESCE(bk.certid, user_info.userid)), 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), (CASE WHEN ((hi.housecategory)::text = '0'::text) THEN '网约房'::text WHEN ((hi.housecategory)::text = '2'::text) THEN '企业宿舍'::text ELSE NULL::text END), li.lockid, (count(DISTINCT opendoorrecord.opentime)), lk.useraccount, (max((opendoorrecord.opentime)::text))
  • Sort Method: quicksort Memory: 2633kB
3. 13.256 2,530.245 ↑ 3.4 4,733 1

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

  • Hash Cond: (((opendoorrecord.lockid)::text = (li.lockid)::text) AND ((opendoorrecord.userid)::text = (lk.useraccount)::text))
4. 594.991 2,412.396 ↑ 4.6 13,922 1

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

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
5. 1,262.325 1,817.405 ↓ 1.0 631,232 1

Sort (cost=77,253.65..78,813.03 rows=623,753 width=51) (actual time=1,665.248..1,817.405 rows=631,232 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid
  • Sort Method: external merge Disk: 37752kB
6. 555.080 555.080 ↓ 1.0 631,232 1

Seq Scan on opendoorrecord (cost=0.00..17,215.50 rows=623,753 width=51) (actual time=0.019..555.080 rows=631,232 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2101kB
8. 5.868 100.328 ↑ 3.4 4,733 1

Hash Left Join (cost=1,434.39..2,507.18 rows=16,060 width=853) (actual time=14.997..100.328 rows=4,733 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: 4828
9. 4.226 93.893 ↑ 1.7 9,561 1

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

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 6.197 87.870 ↑ 1.7 9,561 1

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

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
11. 5.871 77.665 ↑ 1.8 10,795 1

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

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
12. 7.110 69.632 ↑ 1.7 11,757 1

Hash Left Join (cost=1,106.37..1,721.63 rows=19,830 width=932) (actual time=6.366..69.632 rows=11,757 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 (COALESCE(bk.certid, user_info.userid, user_info.account) IS NOT NULL))
  • Rows Removed by Filter: 885
13.          

CTE all_key

14. 2.462 31.855 ↓ 1.0 20,407 1

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

15. 24.811 24.811 ↓ 1.0 10,292 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..640.44 rows=10,218 width=239) (actual time=0.024..24.811 rows=10,292 loops=1)

16. 4.582 4.582 ↑ 1.0 10,115 1

Seq Scan on keyhistoryinfo khi (cost=0.00..139.95 rows=10,115 width=226) (actual time=0.011..4.582 rows=10,115 loops=1)

17. 7.898 62.450 ↑ 1.6 12,642 1

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

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
18. 48.326 48.326 ↑ 1.6 12,642 1

CTE Scan on all_key lk (cost=0.00..457.49 rows=20,030 width=398) (actual time=0.029..48.326 rows=12,642 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 865kB
20. 3.691 3.691 ↓ 1.0 8,338 1

Seq Scan on appuseraccount user_info (cost=0.00..117.94 rows=8,334 width=120) (actual time=0.006..3.691 rows=8,338 loops=1)

21. 0.036 0.072 ↓ 1.0 77 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
22. 0.036 0.036 ↓ 1.0 77 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..1.06 rows=76 width=367) (actual time=0.011..0.036 rows=77 loops=1)

23. 0.846 2.162 ↑ 1.0 3,707 1

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

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

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

  • Filter: (lockid IS NOT NULL)
25. 1.251 4.008 ↑ 1.0 3,842 1

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

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

Seq Scan on houseinfo hi (cost=0.00..80.74 rows=3,885 width=97) (actual time=0.013..2.757 rows=3,842 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
28. 0.536 1.417 ↓ 1.0 1,563 1

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

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

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

30. 0.368 0.702 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
31. 0.334 0.334 ↑ 1.0 1,546 1

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

32. 0.310 0.567 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
33. 0.257 0.257 ↑ 1.0 1,202 1

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

Planning time : 2.209 ms
Execution time : 2,549.751 ms