explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JOfb

Settings
# exclusive inclusive rows x rows loops node
1. 228.414 16,711.607 ↑ 18.1 14,461 1

Sort (cost=106,166.50..106,821.94 rows=262,178 width=320) (actual time=16,709.018..16,711.607 rows=14,461 loops=1)

  • Sort Key: a.house_createtime DESC
  • Sort Method: quicksort Memory: 7143kB
2. 76.359 16,483.193 ↑ 18.1 14,461 1

Subquery Scan on a (cost=48,579.15..80,368.23 rows=262,178 width=320) (actual time=11,802.907..16,483.193 rows=14,461 loops=1)

3. 2,843.239 16,406.834 ↑ 18.1 14,461 1

GroupAggregate (cost=48,579.15..73,158.34 rows=262,178 width=293) (actual time=11,802.900..16,406.834 rows=14,461 loops=1)

  • Group Key: hi.detailaddr, hi.ownername, hi.ownerphone, hi.ownerid, hi.xzqhdm, xz.""全称"", (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)
4. 5,395.427 13,563.595 ↑ 1.6 162,594 1

Sort (cost=48,579.15..49,234.59 rows=262,178 width=391) (actual time=11,801.651..13,563.595 rows=162,594 loops=1)

  • Sort Key: hi.detailaddr, hi.ownername, hi.ownerphone, hi.ownerid, hi.xzqhdm, xz.""全称"", (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)
  • Sort Method: external merge Disk: 52968kB
5. 1,701.223 8,168.168 ↑ 1.6 162,594 1

Hash Left Join (cost=5,793.71..22,320.08 rows=262,178 width=391) (actual time=1,314.340..8,168.168 rows=162,594 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)
  • Rows Removed by Filter: 25634
6. 933.803 6,466.144 ↑ 1.4 188,228 1

Hash Right Join (cost=5,767.26..16,353.32 rows=263,497 width=343) (actual time=1,313.522..6,466.144 rows=188,228 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
7. 531.776 4,810.251 ↑ 1.0 182,801 1

Hash Left Join (cost=3,456.02..11,523.69 rows=183,432 width=148) (actual time=591.381..4,810.251 rows=182,801 loops=1)

  • Hash Cond: (((lk.lockid)::text = (bk.lockid)::text) AND (((COALESCE(lk.keyholder, lk.useraccount)))::text = (bk.bluekeyaccount)::text))
8. 889.301 4,275.819 ↑ 1.0 182,801 1

Hash Left Join (cost=3,372.82..10,477.47 rows=183,432 width=196) (actual time=588.711..4,275.819 rows=182,801 loops=1)

  • Hash Cond: (((COALESCE(lk.keyholder, lk.useraccount)))::text = (ac.account)::text)
9. 811.701 2,996.671 ↑ 1.0 182,801 1

Hash Right Join (cost=974.00..7,597.12 rows=183,432 width=167) (actual time=198.758..2,996.671 rows=182,801 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
10. 303.956 1,986.285 ↑ 1.0 181,581 1

Append (cost=0.00..6,141.55 rows=183,432 width=117) (actual time=0.021..1,986.285 rows=181,581 loops=1)

11. 1,158.259 1,158.259 ↑ 1.0 108,035 1

Seq Scan on lockkeyinfo lk (cost=0.00..4,211.78 rows=109,871 width=117) (actual time=0.020..1,158.259 rows=108,035 loops=1)

12. 524.070 524.070 ↑ 1.0 73,546 1

Seq Scan on keyhistoryinfo hk (cost=0.00..1,012.61 rows=73,561 width=117) (actual time=0.015..524.070 rows=73,546 loops=1)

13. 92.239 198.685 ↑ 1.0 37,139 1

Hash (cost=508.00..508.00 rows=37,280 width=50) (actual time=198.684..198.685 rows=37,139 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3547kB
14. 106.446 106.446 ↑ 1.0 37,139 1

Seq Scan on lockinfo li (cost=0.00..508.00 rows=37,280 width=50) (actual time=0.012..106.446 rows=37,139 loops=1)

15. 149.971 389.847 ↑ 1.0 82,005 1

Hash (cost=1,372.03..1,372.03 rows=82,143 width=29) (actual time=389.847..389.847 rows=82,005 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5986kB
16. 239.876 239.876 ↑ 1.0 82,005 1

Seq Scan on appuseraccount ac (cost=0.00..1,372.03 rows=82,143 width=29) (actual time=0.007..239.876 rows=82,005 loops=1)

17. 1.288 2.656 ↑ 1.0 2,883 1

Hash (cost=39.70..39.70 rows=2,900 width=67) (actual time=2.656..2.656 rows=2,883 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 304kB
18. 1.368 1.368 ↑ 1.0 2,883 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..39.70 rows=2,900 width=67) (actual time=0.014..1.368 rows=2,883 loops=1)

19. 208.923 722.090 ↑ 1.0 53,484 1

Hash (cost=1,641.84..1,641.84 rows=53,552 width=226) (actual time=722.090..722.090 rows=53,484 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 14222kB
20. 164.594 513.167 ↑ 1.0 53,484 1

Hash Left Join (cost=56.19..1,641.84 rows=53,552 width=226) (actual time=1.980..513.167 rows=53,484 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
21. 346.611 346.611 ↑ 1.0 53,484 1

Seq Scan on houseinfo hi (cost=0.00..853.72 rows=53,552 width=178) (actual time=0.010..346.611 rows=53,484 loops=1)

22. 0.414 1.962 ↓ 1.0 1,149 1

Hash (cost=42.01..42.01 rows=1,134 width=60) (actual time=1.962..1.962 rows=1,149 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
23. 0.662 1.548 ↓ 1.0 1,149 1

Hash Right Join (cost=27.12..42.01 rows=1,134 width=60) (actual time=0.703..1.548 rows=1,149 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
24. 0.195 0.195 ↑ 1.0 1,092 1

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

25. 0.369 0.691 ↑ 1.0 1,134 1

Hash (cost=12.94..12.94 rows=1,134 width=60) (actual time=0.690..0.691 rows=1,134 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
26. 0.322 0.322 ↑ 1.0 1,134 1

Seq Scan on "行政区划代码" xz (cost=0.00..12.94 rows=1,134 width=60) (actual time=0.007..0.322 rows=1,134 loops=1)

27. 0.450 0.801 ↑ 1.0 1,100 1

Hash (cost=12.70..12.70 rows=1,100 width=89) (actual time=0.801..0.801 rows=1,100 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 145kB
28. 0.351 0.351 ↑ 1.0 1,100 1

Seq Scan on "管理单位代码" gl (cost=0.00..12.70 rows=1,100 width=89) (actual time=0.009..0.351 rows=1,100 loops=1)

Planning time : 3.575 ms
Execution time : 16,837.752 ms