explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WYsH

Settings
# exclusive inclusive rows x rows loops node
1. 340.918 2,199.694 ↑ 1.2 180,573 1

Hash Left Join (cost=27,817.45..52,510.92 rows=219,099 width=1,270) (actual time=596.751..2,199.694 rows=180,573 loops=1)

  • Hash Cond: (((li.lockid)::text = opendoor_summary_mrel.lockid) AND ((lk.useraccount)::text = opendoor_summary_mrel.userid))
2. 191.389 1,598.491 ↑ 1.2 180,573 1

Hash Join (cost=17,359.23..31,042.97 rows=219,099 width=1,130) (actual time=335.817..1,598.491 rows=180,573 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
3. 298.408 1,173.178 ↑ 1.0 217,409 1

Hash Left Join (cost=13,092.55..21,552.74 rows=220,550 width=955) (actual time=101.844..1,173.178 rows=217,409 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 (CASE WHEN (substr((COALESCE(bk.certid, user_info.userid, user_info.account))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, user_info.userid, user_info.account))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, user_info.userid, user_info.account) END IS NOT NULL))
  • Rows Removed by Filter: 12920
4.          

CTE all_key

5. 29.174 356.875 ↓ 1.0 230,329 1

Append (cost=0.00..9,991.85 rows=225,017 width=232) (actual time=0.030..356.875 rows=230,329 loops=1)

6. 271.151 271.151 ↑ 1.0 120,409 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..7,378.32 rows=120,522 width=238) (actual time=0.029..271.151 rows=120,409 loops=1)

7. 56.550 56.550 ↓ 1.1 109,920 1

Seq Scan on keyhistoryinfo khi (cost=0.00..1,488.45 rows=104,495 width=225) (actual time=0.017..56.550 rows=109,920 loops=1)

8. 193.022 871.211 ↓ 1.0 230,329 1

Hash Left Join (cost=2,999.27..8,084.41 rows=222,772 width=648) (actual time=98.263..871.211 rows=230,329 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
9. 580.087 580.087 ↓ 1.0 230,329 1

CTE Scan on all_key lk (cost=0.00..4,500.34 rows=222,772 width=504) (actual time=0.036..580.087 rows=230,329 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL))
10. 38.497 98.102 ↑ 1.0 103,601 1

Hash (cost=1,704.01..1,704.01 rows=103,621 width=144) (actual time=98.102..98.102 rows=103,601 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18462kB
11. 59.605 59.605 ↑ 1.0 103,601 1

Seq Scan on appuseraccount user_info (cost=0.00..1,704.01 rows=103,621 width=144) (actual time=0.010..59.605 rows=103,601 loops=1)

12. 1.887 3.559 ↓ 1.0 3,553 1

Hash (cost=48.73..48.73 rows=3,513 width=336) (actual time=3.559..3.559 rows=3,553 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 878kB
13. 1.672 1.672 ↓ 1.0 3,553 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..48.73 rows=3,513 width=336) (actual time=0.008..1.672 rows=3,553 loops=1)

14. 31.628 233.924 ↑ 1.1 43,137 1

Hash (cost=3,675.86..3,675.86 rows=47,266 width=305) (actual time=233.924..233.924 rows=43,137 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 15179kB
15. 35.112 202.296 ↑ 1.1 43,137 1

Hash Left Join (cost=2,116.64..3,675.86 rows=47,266 width=305) (actual time=86.412..202.296 rows=43,137 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: 2029
16. 24.525 166.427 ↑ 1.1 45,166 1

Hash Left Join (cost=2,087.39..3,521.07 rows=47,503 width=247) (actual time=85.641..166.427 rows=45,166 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
17. 42.656 139.677 ↑ 1.1 45,166 1

Hash Join (cost=2,014.38..2,798.37 rows=47,503 width=183) (actual time=83.404..139.677 rows=45,166 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
18. 13.745 13.745 ↑ 1.0 47,188 1

Seq Scan on lockinfo li (cost=0.00..659.09 rows=47,579 width=51) (actual time=0.010..13.745 rows=47,188 loops=1)

  • Filter: (lockid IS NOT NULL)
19. 37.831 83.276 ↓ 1.0 69,869 1

Hash (cost=1,145.89..1,145.89 rows=69,479 width=164) (actual time=83.276..83.276 rows=69,869 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 14088kB
20. 45.445 45.445 ↓ 1.0 69,869 1

Seq Scan on houseinfo hi (cost=0.00..1,145.89 rows=69,479 width=164) (actual time=0.005..45.445 rows=69,869 loops=1)

21. 0.508 2.225 ↓ 1.0 1,563 1

Hash (cost=53.53..53.53 rows=1,559 width=76) (actual time=2.225..2.225 rows=1,563 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 183kB
22. 0.694 1.717 ↓ 1.0 1,563 1

Hash Right Join (cost=37.28..53.53 rows=1,559 width=76) (actual time=0.856..1.717 rows=1,563 loops=1)

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

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

24. 0.464 0.840 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 186kB
25. 0.376 0.376 ↑ 1.0 1,546 1

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

26. 0.435 0.757 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
27. 0.322 0.322 ↑ 1.0 1,202 1

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

28. 85.766 260.285 ↓ 1.1 196,575 1

Hash (cost=7,857.10..7,857.10 rows=173,408 width=54) (actual time=260.285..260.285 rows=196,575 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 18969kB
29. 174.519 174.519 ↓ 1.2 199,615 1

Seq Scan on opendoor_summary_mrel (cost=0.00..7,857.10 rows=173,408 width=54) (actual time=0.032..174.519 rows=199,615 loops=1)

Planning time : 2.400 ms
Execution time : 2,221.456 ms