explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uuyM

Settings
# exclusive inclusive rows x rows loops node
1. 1,209.419 4,427.136 ↑ 1.0 1 1

Aggregate (cost=4,201.73..4,201.74 rows=1 width=24) (actual time=4,427.136..4,427.136 rows=1 loops=1)

2. 358.085 3,217.717 ↓ 89.1 224,915 1

Gather (cost=1,229.80..3,980.88 rows=2,524 width=250) (actual time=20.449..3,217.717 rows=224,915 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 142.945 2,859.632 ↓ 71.3 74,972 3 / 3

Hash Left Join (cost=229.80..2,728.48 rows=1,052 width=250) (actual time=21.591..2,859.632 rows=74,972 loops=3)

  • Hash Cond: (((lk.lockid)::text = (bk.lockid)::text) AND (((COALESCE(lk.keyholder, lk.useraccount)))::text = (bk.bluekeyaccount)::text))
4. 98.571 2,703.923 ↓ 71.3 74,972 3 / 3

Nested Loop Left Join (cost=108.45..2,601.61 rows=1,052 width=309) (actual time=8.794..2,703.923 rows=74,972 loops=3)

5. 84.357 228.174 ↓ 86.8 17,352 3 / 3

Hash Left Join (cost=107.20..1,783.29 rows=200 width=74) (actual time=8.689..228.174 rows=17,352 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: 14703
6. 98.548 142.858 ↑ 1.3 32,054 3 / 3

Hash Left Join (cost=77.48..1,646.98 rows=40,182 width=62) (actual time=7.707..142.858 rows=32,054 loops=3)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
7. 36.703 36.703 ↑ 1.3 32,054 3 / 3

Parallel Seq Scan on houseinfo hi (cost=0.00..1,019.12 rows=40,182 width=46) (actual time=0.014..36.703 rows=32,054 loops=3)

8. 1.684 7.607 ↓ 1.0 1,639 3 / 3

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

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

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

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

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

11. 0.512 1.118 ↓ 1.0 1,632 3 / 3

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

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

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

13. 0.424 0.959 ↑ 1.0 1,223 3 / 3

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

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

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

15. 171.463 2,377.178 ↑ 1.2 4 52,055 / 3

Nested Loop Left Join (cost=1.26..4.04 rows=5 width=268) (actual time=0.047..0.137 rows=4 loops=52,055)

16. 104.882 1,231.968 ↑ 1.2 4 52,055 / 3

Nested Loop Left Join (cost=0.84..1.84 rows=5 width=238) (actual time=0.034..0.071 rows=4 loops=52,055)

17. 208.220 208.220 ↑ 1.0 1 52,055 / 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.011..0.012 rows=1 loops=52,055)

  • Index Cond: ((hi.houseid)::text = (houseid)::text)
18. 59.667 918.867 ↑ 2.0 6 35,800 / 3

Append (cost=0.42..1.28 rows=12 width=186) (actual time=0.029..0.077 rows=6 loops=35,800)

19. 513.133 513.133 ↑ 1.5 2 35,800 / 3

Index Scan using idx_idx_lockid_lockkeyinf_1578488597_12 on lockkeyinfo lk (cost=0.42..0.63 rows=3 width=193) (actual time=0.027..0.043 rows=2 loops=35,800)

  • Index Cond: ((lockid)::text = (li.lockid)::text)
20. 346.067 346.067 ↑ 3.0 3 35,800 / 3

Index Scan using idx_idx_lockid_keyhistory_1578488537_4 on keyhistoryinfo hk (cost=0.42..0.59 rows=9 width=181) (actual time=0.018..0.029 rows=3 loops=35,800)

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

Index Scan using "pk_appuseracc_1578488483_2 " on appuseraccount ac (cost=0.42..0.44 rows=1 width=30) (actual time=0.014..0.014 rows=1 loops=208,660)

  • Index Cond: ((account)::text = ((COALESCE(lk.keyholder, lk.useraccount)))::text)
22. 8.508 12.764 ↓ 1.0 4,228 3 / 3

Hash (cost=58.02..58.02 rows=4,222 width=56) (actual time=12.763..12.764 rows=4,228 loops=3)

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

Seq Scan on bluekeyuserinfo bk (cost=0.00..58.02 rows=4,222 width=56) (actual time=0.015..4.256 rows=4,228 loops=3)