explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWQK

Settings
# exclusive inclusive rows x rows loops node
1. 100.481 2,597.551 ↑ 1.6 131,578 1

Unique (cost=74,520.11..86,445.87 rows=216,832 width=937) (actual time=2,437.041..2,597.551 rows=131,578 loops=1)

2. 630.378 2,497.070 ↑ 1.6 132,933 1

Sort (cost=74,520.11..75,062.19 rows=216,832 width=937) (actual time=2,437.038..2,497.070 rows=132,933 loops=1)

  • Sort Key: (COALESCE(user_info.headpicture, bk.certfrontpic, bk.certbackpic)), (COALESCE(user_info.username, bk.username)), (CASE WHEN (substr((COALESCE(bk.certid, user_info.userid))::text, 18, 1) = 'x'::text) THEN ((substr((COALESCE(bk.certid, user_info.userid))::text, 1, 17) || 'X'::text))::character varying ELSE COALESCE(bk.certid, user_info.userid) END), user_info.authstate, (COALESCE(user_info.account, bk.keyholder)), lk.authtime, (CASE WHEN (lk.valid_state = '1'::text) THEN lk.stoptime WHEN (lk.valid_state = '0'::text) THEN lk.expireddate ELSE NULL::character varying END), lk.valid_state, lk.authaccount, lk.keytype, hi.detailaddr, hi.housenumber, xz.""全称"", hi.xzqhdm, (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END), (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""名称"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位名称"" ELSE NULL::text END), li.lockid, (pipelinedb.finalize('extensions.hll_count_distinct'::text, '{pg_catalog.any}'::text[], opendoor_summary_mrel.distinct_open_counts, NULL::bigint)), lk.useraccount, hi.jwq, (((opendoor_summary_mrel._0)::timestamp(0) without time zone)::text)
  • Sort Method: external merge Disk: 60080kB
3. 230.953 1,866.692 ↑ 1.6 132,933 1

Hash Left Join (cost=27,807.90..50,177.66 rows=216,832 width=937) (actual time=532.987..1,866.692 rows=132,933 loops=1)

  • Hash Cond: (((li.lockid)::text = opendoor_summary_mrel.lockid) AND ((lk.useraccount)::text = opendoor_summary_mrel.userid))
4. 140.340 1,377.709 ↑ 1.6 132,933 1

Hash Join (cost=17,349.68..31,534.03 rows=216,832 width=898) (actual time=274.765..1,377.709 rows=132,933 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
5. 225.004 1,044.712 ↑ 1.4 160,565 1

Hash Left Join (cost=13,086.73..22,086.43 rows=219,369 width=955) (actual time=82.060..1,044.712 rows=160,565 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: 12911
6.          

CTE all_key

7. 29.728 368.163 ↓ 1.0 230,249 1

Append (cost=0.00..9,986.49 rows=224,936 width=232) (actual time=0.027..368.163 rows=230,249 loops=1)

8. 282.601 282.601 ↑ 1.0 120,362 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..7,373.36 rows=120,441 width=238) (actual time=0.026..282.601 rows=120,362 loops=1)

9. 55.834 55.834 ↓ 1.1 109,887 1

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

10. 141.683 817.218 ↑ 1.3 173,476 1

Hash Left Join (cost=2,998.81..8,641.54 rows=221,579 width=522) (actual time=79.551..817.218 rows=173,476 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
11. 596.096 596.096 ↑ 1.3 173,476 1

CTE Scan on all_key lk (cost=0.00..5,061.06 rows=221,579 width=398) (actual time=0.032..596.096 rows=173,476 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
  • Rows Removed by Filter: 56773
12. 36.401 79.439 ↑ 1.0 103,578 1

Hash (cost=1,703.75..1,703.75 rows=103,605 width=124) (actual time=79.438..79.439 rows=103,578 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16435kB
13. 43.038 43.038 ↑ 1.0 103,578 1

Seq Scan on appuseraccount user_info (cost=0.00..1,703.75 rows=103,605 width=124) (actual time=0.006..43.038 rows=103,578 loops=1)

14. 1.305 2.490 ↓ 1.0 3,551 1

Hash (cost=48.73..48.73 rows=3,513 width=280) (actual time=2.490..2.490 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 877kB
15. 1.185 1.185 ↓ 1.0 3,551 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..48.73 rows=3,513 width=280) (actual time=0.008..1.185 rows=3,551 loops=1)

16. 23.676 192.657 ↑ 1.1 43,117 1

Hash (cost=3,675.40..3,675.40 rows=47,004 width=235) (actual time=192.657..192.657 rows=43,117 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 12081kB
17. 37.856 168.981 ↑ 1.1 43,117 1

Hash Left Join (cost=2,116.64..3,675.40 rows=47,004 width=235) (actual time=64.375..168.981 rows=43,117 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) AND (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
18. 19.472 130.524 ↑ 1.1 45,146 1

Hash Left Join (cost=2,087.39..3,520.31 rows=47,478 width=177) (actual time=63.764..130.524 rows=45,146 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz."代码")
19. 35.557 109.489 ↑ 1.1 45,146 1

Hash Join (cost=2,014.38..2,797.96 rows=47,478 width=129) (actual time=62.189..109.489 rows=45,146 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
20. 11.842 11.842 ↑ 1.0 47,168 1

Seq Scan on lockinfo li (cost=0.00..658.74 rows=47,554 width=51) (actual time=0.009..11.842 rows=47,168 loops=1)

  • Filter: (lockid IS NOT NULL)
21. 26.448 62.090 ↓ 1.0 69,866 1

Hash (cost=1,145.89..1,145.89 rows=69,479 width=143) (actual time=62.090..62.090 rows=69,866 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 12602kB
22. 35.642 35.642 ↓ 1.0 69,866 1

Seq Scan on houseinfo hi (cost=0.00..1,145.89 rows=69,479 width=143) (actual time=0.005..35.642 rows=69,866 loops=1)

23. 0.346 1.563 ↓ 1.0 1,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 157kB
24. 0.498 1.217 ↓ 1.0 1,563 1

Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual time=0.610..1.217 rows=1,563 loops=1)

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

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

26. 0.322 0.579 ↑ 1.0 1,546 1

Hash (cost=17.79..17.79 rows=1,559 width=60) (actual time=0.578..0.579 rows=1,546 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
27. 0.257 0.257 ↑ 1.0 1,546 1

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

28. 0.355 0.601 ↑ 1.0 1,202 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
29. 0.246 0.246 ↑ 1.0 1,202 1

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

30. 82.224 258.030 ↓ 1.1 196,526 1

Hash (cost=7,857.10..7,857.10 rows=173,408 width=46) (actual time=258.030..258.030 rows=196,526 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 17429kB
31. 175.806 175.806 ↓ 1.2 199,563 1

Seq Scan on opendoor_summary_mrel (cost=0.00..7,857.10 rows=173,408 width=46) (actual time=0.035..175.806 rows=199,563 loops=1)

Planning time : 2.720 ms
Execution time : 2,622.465 ms