explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d4yaj

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,085.768 ↑ 1.0 2 1

Append (cost=3,015.39..3,357.92 rows=2 width=48) (actual time=1,007.424..1,085.768 rows=2 loops=1)

2. 153.043 1,007.423 ↑ 1.0 1 1

Aggregate (cost=3,015.39..3,015.40 rows=1 width=48) (actual time=1,007.423..1,007.423 rows=1 loops=1)

3. 0.000 854.380 ↓ 166.0 52,292 1

Gather (cost=1,108.73..3,004.37 rows=315 width=102) (actual time=5.496..854.380 rows=52,292 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 33.442 886.573 ↓ 133.1 17,431 3 / 3

Nested Loop Left Join (cost=108.73..1,972.87 rows=131 width=102) (actual time=8.163..886.573 rows=17,431 loops=3)

5. 35.566 783.408 ↓ 133.1 17,431 3 / 3

Nested Loop Left Join (cost=108.45..1,931.84 rows=131 width=127) (actual time=8.148..783.408 rows=17,431 loops=3)

6. 38.763 608.397 ↓ 133.1 17,431 3 / 3

Nested Loop Left Join (cost=108.03..1,869.64 rows=131 width=114) (actual time=8.128..608.397 rows=17,431 loops=3)

7. 22.169 295.367 ↓ 91.0 11,925 3 / 3

Nested Loop (cost=107.61..1,798.91 rows=131 width=53) (actual time=8.070..295.367 rows=11,925 loops=3)

8. 48.235 134.481 ↓ 90.3 17,340 3 / 3

Hash Left Join (cost=107.20..1,713.28 rows=192 width=34) (actual time=8.007..134.481 rows=17,340 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 ~~ '330522%'::text)
  • Rows Removed by Filter: 13471
9. 47.972 84.268 ↑ 1.2 30,811 3 / 3

Hash Left Join (cost=77.48..1,581.78 rows=38,495 width=62) (actual time=6.003..84.268 rows=30,811 loops=3)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
10. 30.389 30.389 ↑ 1.2 30,811 3 / 3

Parallel Seq Scan on houseinfo hi (cost=0.00..977.05 rows=38,495 width=46) (actual time=0.009..30.389 rows=30,811 loops=3)

11. 1.436 5.907 ↓ 1.0 1,639 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
12. 1.190 4.471 ↓ 1.0 1,639 3 / 3

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

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

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

14. 1.593 2.106 ↓ 1.0 1,632 3 / 3

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

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

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

16. 0.640 1.978 ↑ 1.0 1,223 3 / 3

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

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

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

18. 138.717 138.717 ↑ 1.0 1 52,019 / 3

Index Scan using idx_index_hous_lockinfo_1578488557_8 on lockinfo li (cost=0.41..0.44 rows=1 width=52) (actual time=0.008..0.008 rows=1 loops=52,019)

  • Index Cond: ((houseid)::text = (hi.houseid)::text)
19. 274.267 274.267 ↑ 1.0 1 35,774 / 3

Index Scan using idx_idx_lockid_lockkeyinf_1578488597_12 on lockkeyinfo (cost=0.42..0.53 rows=1 width=80) (actual time=0.019..0.023 rows=1 loops=35,774)

  • Index Cond: ((li.lockid)::text = (lockid)::text)
  • Filter: ((userrole <> 255) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 1
20. 139.445 139.445 ↑ 1.0 1 52,292 / 3

Index Scan using "pk_appuseracc_1578488483_2 " on appuseraccount ac (cost=0.42..0.47 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=52,292)

  • Index Cond: ((account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
21. 69.723 69.723 ↓ 0.0 0 52,292 / 3

Index Scan using "pk_bluekeyuse_1570385661_103 " on bluekeyuserinfo bk (cost=0.28..0.30 rows=1 width=56) (actual time=0.004..0.004 rows=0 loops=52,292)

  • Index Cond: ((bluekeyaccount)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
  • Filter: ((lockkeyinfo.lockid)::text = (lockid)::text)
  • Rows Removed by Filter: 0
22. 7.126 78.341 ↑ 1.0 1 1

Aggregate (cost=342.47..342.48 rows=1 width=48) (actual time=78.341..78.341 rows=1 loops=1)

23. 3.074 71.215 ↓ 204.6 4,502 1

Nested Loop Left Join (cost=108.19..341.70 rows=22 width=101) (actual time=2.499..71.215 rows=4,502 loops=1)

24. 3.683 63.639 ↓ 204.6 4,502 1

Nested Loop Left Join (cost=108.05..337.90 rows=22 width=128) (actual time=2.491..63.639 rows=4,502 loops=1)

25. 1.538 46.450 ↓ 204.6 4,502 1

Nested Loop Left Join (cost=107.77..330.87 rows=22 width=114) (actual time=2.479..46.450 rows=4,502 loops=1)

26. 1.611 24.056 ↓ 118.5 2,607 1

Nested Loop (cost=107.48..320.34 rows=22 width=52) (actual time=2.455..24.056 rows=2,607 loops=1)

27. 3.089 11.981 ↓ 120.3 3,488 1

Hash Left Join (cost=107.20..311.23 rows=29 width=33) (actual time=2.435..11.981 rows=3,488 loops=1)

  • Hash Cond: (ys_1."管理单位代码_id" = gl_1.id)
  • Filter: (CASE WHEN (length((hi_1.xzqhdm)::text) = 9) THEN gl_1."代码" WHEN (length((hi_1.xzqhdm)::text) = 12) THEN gl_1."上级单位代码" ELSE NULL::text END ~~ '330522%'::text)
  • Rows Removed by Filter: 2462
28. 2.668 8.324 ↓ 1.0 5,950 1

Hash Left Join (cost=77.48..265.91 rows=5,899 width=61) (actual time=1.852..8.324 rows=5,950 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
29. 3.834 3.834 ↓ 1.0 5,950 1

Seq Scan on houseinfo hi_1 (cost=0.00..133.14 rows=5,899 width=45) (actual time=0.018..3.834 rows=5,950 loops=1)

  • Filter: ((housecategory)::text = '2'::text)
  • Rows Removed by Filter: 1907
30. 0.381 1.822 ↓ 1.0 1,639 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
31. 0.600 1.441 ↓ 1.0 1,639 1

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

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

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

33. 0.344 0.669 ↓ 1.0 1,632 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
34. 0.325 0.325 ↓ 1.0 1,632 1

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

35. 0.296 0.568 ↑ 1.0 1,223 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
36. 0.272 0.272 ↑ 1.0 1,223 1

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

37. 10.464 10.464 ↑ 1.0 1 3,488

Index Scan using idx_index_hous_lockinfo_1575546514_2 on lockinfo li_1 (cost=0.28..0.30 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=3,488)

  • Index Cond: ((houseid)::text = (hi_1.houseid)::text)
38. 20.856 20.856 ↓ 2.0 2 2,607

Index Scan using lockkeyinfo_lockid_idx on lockkeyinfo lockkeyinfo_1 (cost=0.29..0.47 rows=1 width=81) (actual time=0.005..0.008 rows=2 loops=2,607)

  • Index Cond: ((li_1.lockid)::text = (lockid)::text)
  • Filter: ((userrole <> 255) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 1
39. 13.506 13.506 ↑ 1.0 1 4,502

Index Scan using "pk_appuseracc_1570384907_16 " on appuseraccount ac_1 (cost=0.29..0.32 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=4,502)

  • Index Cond: ((account)::text = (COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text)
40. 4.502 4.502 ↓ 0.0 0 4,502

Index Scan using "pk_bluekeyuse_1570384910_19 " on bluekeyuserinfo bk_1 (cost=0.14..0.16 rows=1 width=57) (actual time=0.001..0.001 rows=0 loops=4,502)

  • Index Cond: ((bluekeyaccount)::text = (COALESCE(lockkeyinfo_1.keyholder, lockkeyinfo_1.useraccount))::text)
  • Filter: ((lockkeyinfo_1.lockid)::text = (lockid)::text)