explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XkOU

Settings
# exclusive inclusive rows x rows loops node
1. 389.643 1,869.357 ↑ 1.2 180,584 1

Hash Join (cost=17,359.23..39,806.93 rows=219,099 width=1,216) (actual time=323.051..1,869.357 rows=180,584 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
2. 328.721 1,264.837 ↑ 1.0 217,421 1

Hash Left Join (cost=13,092.55..21,552.74 rows=220,550 width=955) (actual time=108.122..1,264.837 rows=217,421 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: 12923
3.          

CTE all_key

4. 31.223 358.579 ↓ 1.0 230,344 1

Append (cost=0.00..9,991.85 rows=225,017 width=232) (actual time=0.047..358.579 rows=230,344 loops=1)

5. 259.388 259.388 ↑ 1.0 120,415 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..7,378.32 rows=120,522 width=238) (actual time=0.046..259.388 rows=120,415 loops=1)

6. 67.968 67.968 ↓ 1.1 109,929 1

Seq Scan on keyhistoryinfo khi (cost=0.00..1,488.45 rows=104,495 width=225) (actual time=0.012..67.968 rows=109,929 loops=1)

7. 209.831 933.032 ↓ 1.0 230,344 1

Hash Left Join (cost=2,999.27..8,084.41 rows=222,772 width=648) (actual time=105.017..933.032 rows=230,344 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
8. 618.330 618.330 ↓ 1.0 230,344 1

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

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL))
9. 41.016 104.871 ↑ 1.0 103,602 1

Hash (cost=1,704.01..1,704.01 rows=103,621 width=144) (actual time=104.870..104.871 rows=103,602 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18462kB
10. 63.855 63.855 ↑ 1.0 103,602 1

Seq Scan on appuseraccount user_info (cost=0.00..1,704.01 rows=103,621 width=144) (actual time=0.007..63.855 rows=103,602 loops=1)

11. 1.723 3.084 ↓ 1.0 3,554 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 878kB
12. 1.361 1.361 ↓ 1.0 3,554 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..48.73 rows=3,513 width=336) (actual time=0.009..1.361 rows=3,554 loops=1)

13. 29.204 214.877 ↑ 1.1 43,139 1

Hash (cost=3,675.86..3,675.86 rows=47,266 width=305) (actual time=214.876..214.877 rows=43,139 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 15179kB
14. 33.614 185.673 ↑ 1.1 43,139 1

Hash Left Join (cost=2,116.64..3,675.86 rows=47,266 width=305) (actual time=75.781..185.673 rows=43,139 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
15. 23.110 151.461 ↑ 1.1 45,168 1

Hash Left Join (cost=2,087.39..3,521.07 rows=47,503 width=247) (actual time=75.173..151.461 rows=45,168 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
16. 40.129 126.321 ↑ 1.1 45,168 1

Hash Join (cost=2,014.38..2,798.37 rows=47,503 width=183) (actual time=73.130..126.321 rows=45,168 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
17. 13.160 13.160 ↑ 1.0 47,190 1

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

  • Filter: (lockid IS NOT NULL)
18. 32.467 73.032 ↓ 1.0 69,869 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 14088kB
19. 40.565 40.565 ↓ 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..40.565 rows=69,869 loops=1)

20. 0.470 2.030 ↓ 1.0 1,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 183kB
21. 0.561 1.560 ↓ 1.0 1,563 1

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

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

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

23. 0.508 0.834 ↑ 1.0 1,546 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 186kB
24. 0.326 0.326 ↑ 1.0 1,546 1

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

25. 0.337 0.598 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
26. 0.261 0.261 ↑ 1.0 1,202 1

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

Planning time : 1.723 ms
Execution time : 1,894.980 ms