explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aWOM

Settings
# exclusive inclusive rows x rows loops node
1. 878.084 6,626.047 ↑ 1.0 1 1

Aggregate (cost=4,072.63..4,072.64 rows=1 width=16) (actual time=6,626.047..6,626.047 rows=1 loops=1)

2. 203.713 5,747.963 ↓ 89.6 224,377 1

Gather (cost=1,229.80..3,959.91 rows=2,505 width=156) (actual time=163.126..5,747.963 rows=224,377 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 70.762 5,544.250 ↓ 71.6 74,792 3 / 3

Hash Left Join (cost=229.80..2,709.41 rows=1,044 width=156) (actual time=119.806..5,544.250 rows=74,792 loops=3)

  • Hash Cond: (((lk.lockid)::text = (bk.lockid)::text) AND (((COALESCE(lk.keyholder, lk.useraccount)))::text = (bk.bluekeyaccount)::text))
4. 56.219 5,455.379 ↓ 71.6 74,792 3 / 3

Nested Loop Left Join (cost=108.45..2,582.58 rows=1,044 width=215) (actual time=101.653..5,455.379 rows=74,792 loops=3)

5. 62.791 1,235.560 ↓ 86.7 17,348 3 / 3

Hash Left Join (cost=107.20..1,779.23 rows=200 width=34) (actual time=56.274..1,235.560 rows=17,348 loops=3)

  • 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 ~~ '330522%'::text))
  • Rows Removed by Filter: 14634
6. 56.916 1,159.112 ↑ 1.3 31,983 3 / 3

Hash Left Join (cost=77.48..1,643.18 rows=40,085 width=62) (actual time=42.595..1,159.112 rows=31,983 loops=3)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
7. 1,059.771 1,059.771 ↑ 1.3 31,983 3 / 3

Parallel Seq Scan on houseinfo hi (cost=0.00..1,016.65 rows=40,085 width=46) (actual time=0.018..1,059.771 rows=31,983 loops=3)

8. 0.521 42.425 ↓ 1.0 1,639 3 / 3

Hash (cost=57.10..57.10 rows=1,630 width=28) (actual time=42.425..42.425 rows=1,639 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
9. 15.980 41.904 ↓ 1.0 1,639 3 / 3

Hash Right Join (cost=38.98..57.10 rows=1,630 width=28) (actual time=40.965..41.904 rows=1,639 loops=3)

  • Hash Cond: (ys."行政区划_id" = xz.id)
10. 5.804 5.804 ↑ 1.0 1,332 3 / 3

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

11. 0.551 20.120 ↓ 1.0 1,632 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
12. 19.569 19.569 ↓ 1.0 1,632 3 / 3

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

13. 0.434 13.657 ↑ 1.0 1,223 3 / 3

Hash (cost=14.43..14.43 rows=1,223 width=44) (actual time=13.657..13.657 rows=1,223 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
14. 13.223 13.223 ↑ 1.0 1,223 3 / 3

Seq Scan on "管理单位代码" gl (cost=0.00..14.43 rows=1,223 width=44) (actual time=12.807..13.223 rows=1,223 loops=3)

15. 69.924 4,163.600 ↑ 1.2 4 52,045 / 3

Nested Loop Left Join (cost=1.26..3.97 rows=5 width=214) (actual time=0.070..0.240 rows=4 loops=52,045)

16. 62.495 2,220.587 ↑ 1.2 4 52,045 / 3

Nested Loop Left Join (cost=0.84..1.76 rows=5 width=202) (actual time=0.042..0.128 rows=4 loops=52,045)

17. 416.360 416.360 ↑ 1.0 1 52,045 / 3

Index Scan using idx_index_hous_lockinfo_1578488557_8 on lockinfo li (cost=0.42..0.44 rows=1 width=52) (actual time=0.024..0.024 rows=1 loops=52,045)

  • Index Cond: ((hi.houseid)::text = (houseid)::text)
18. 47.719 1,741.731 ↑ 2.0 6 35,789 / 3

Append (cost=0.42..1.20 rows=12 width=150) (actual time=0.025..0.146 rows=6 loops=35,789)

19. 369.820 369.820 ↑ 1.5 2 35,789 / 3

Index Scan using idx_idx_lockid_lockkeyinf_1578488597_12 on lockkeyinfo lk (cost=0.42..0.55 rows=3 width=157) (actual time=0.023..0.031 rows=2 loops=35,789)

  • Index Cond: ((lockid)::text = (li.lockid)::text)
20. 1,324.193 1,324.193 ↑ 3.0 3 35,789 / 3

Index Scan using idx_idx_lockid_keyhistory_1578488537_4 on keyhistoryinfo hk (cost=0.42..0.59 rows=9 width=145) (actual time=0.045..0.111 rows=3 loops=35,789)

  • Index Cond: ((lockid)::text = (li.lockid)::text)
21. 1,873.089 1,873.089 ↑ 1.0 1 208,121 / 3

Index Only Scan using "pk_appuseracc_1578488483_2 " on appuseraccount ac (cost=0.42..0.44 rows=1 width=12) (actual time=0.027..0.027 rows=1 loops=208,121)

  • Index Cond: (account = ((COALESCE(lk.keyholder, lk.useraccount)))::text)
  • Heap Fetches: 193098
22. 1.611 18.109 ↓ 1.0 4,226 3 / 3

Hash (cost=58.02..58.02 rows=4,222 width=56) (actual time=18.109..18.109 rows=4,226 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 439kB
23. 16.498 16.498 ↓ 1.0 4,226 3 / 3

Seq Scan on bluekeyuserinfo bk (cost=0.00..58.02 rows=4,222 width=56) (actual time=0.023..16.498 rows=4,226 loops=3)