explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TBvO

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=175,942,409.12..175,942,409.17 rows=1 width=820) (actual rows= loops=)

2.          

CTE dates_in_interest

3. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

5.          

CTE bucket

6. 0.000 0.000 ↓ 0.0

CTE Scan on dates_in_interest (cost=0.00..70.00 rows=1,000 width=128) (actual rows= loops=)

7.          

CTE candidates_r2

8. 0.000 0.000 ↓ 0.0

Group (cost=85,474,604.01..90,191,831.79 rows=85,236,800 width=119) (actual rows= loops=)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid, bucket_1.*
9. 0.000 0.000 ↓ 0.0

Sort (cost=85,474,604.01..86,653,910.95 rows=471,722,778 width=119) (actual rows= loops=)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid, bucket_1.*
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..14,198,202.60 rows=471,722,778 width=119) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

CTE Scan on bucket bucket_1 (cost=0.00..20.00 rows=1,000 width=152) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using opendoorrecord_opentime_idx on opendoorrecord (cost=0.43..9,480.95 rows=471,723 width=51) (actual rows= loops=)

  • Index Cond: (((opentime)::text >= bucket_1.r2_from) AND ((opentime)::text < bucket_1.r2_to))
  • Filter: (userid IS NOT NULL)
13.          

CTE candidates

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..70,107,268.00 rows=85,236,800 width=188) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on candidates_r2 (cost=0.00..1,704,736.00 rows=85,236,800 width=188) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.56..0.78 rows=1 width=58) (actual rows= loops=)

  • Index Cond: ((lockid = (candidates_r2.lockid)::text) AND (userid = (candidates_r2.userid)::text) AND (opentime >= (candidates_r2.bucket).r1_from) AND (opentime < (candidates_r2.bucket).r1_to))
17.          

CTE activi_people

18. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=13,831,524.01..14,684,392.01 rows=13,333 width=156) (actual rows= loops=)

  • Group Key: candidates.lockid, candidates.userid
  • Filter: (count(DISTINCT candidates.bucket) >= 3)
19. 0.000 0.000 ↓ 0.0

Sort (cost=13,831,524.01..14,044,616.01 rows=85,236,800 width=188) (actual rows= loops=)

  • Sort Key: candidates.lockid, candidates.userid
20. 0.000 0.000 ↓ 0.0

CTE Scan on candidates (cost=0.00..1,704,736.00 rows=85,236,800 width=188) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=958,842.28..958,842.28 rows=1 width=820) (actual rows= loops=)

  • Sort Key: (uuid_generate_v5('00000000-0000-0000-0000-000000000000'::uuid, (((activi_people.userid)::text || CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END) || (activi_people.lockid)::text))), uk.userid, (COALESCE(uk.account, uk.keyholder)), uk.username, uk.picture, hi.detailaddr, hi.housenumber, activi_people.lockid, ((opendoor_summary_mrel._0)::timestamp(0) without time zone), (count(DISTINCT od.opentime)), hi.xzqhdm, xz.""名称"", (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), (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN NULL::text WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""代码"" ELSE NULL::text END), (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN NULL::text WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""名称"" ELSE NULL::text END), activi_people.userid
22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=930,689.10..958,842.27 rows=1 width=820) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=930,688.68..958,841.59 rows=1 width=863) (actual rows= loops=)

  • 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)
24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=930,688.40..958,841.27 rows=1 width=805) (actual rows= loops=)

  • Join Filter: (ys."行政区划_id" = xz.id)
25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=930,688.40..958,813.27 rows=1 width=805) (actual rows= loops=)

  • Join Filter: (xz."代码" = (hi.xzqhdm)::text)
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=930,688.40..958,775.99 rows=1 width=773) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=930,687.99..958,775.55 rows=1 width=713) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Merge Join (cost=930,687.57..958,737.49 rows=74 width=681) (actual rows= loops=)

  • Merge Cond: (((activi_people.lockid)::text = (uk.lockid)::text) AND ((activi_people.userid)::text = (uk.useraccount)::text))
29. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=913,453.20..941,465.83 rows=1,333 width=164) (actual rows= loops=)

  • Group Key: activi_people.lockid, activi_people.userid
30. 0.000 0.000 ↓ 0.0

Sort (cost=913,453.20..920,453.03 rows=2,799,930 width=176) (actual rows= loops=)

  • Sort Key: activi_people.lockid, activi_people.userid
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..599,951.67 rows=2,799,930 width=176) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

CTE Scan on activi_people (cost=0.00..266.66 rows=13,333 width=156) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..40.78 rows=210 width=20) (actual rows= loops=)

  • Join Filter: ((((od.opentime)::text >= bucket.r2_from) AND ((od.opentime)::text < bucket.r2_to)) OR (((od.opentime)::text >= bucket.r1_from) AND ((od.opentime)::text < bucket.r1_to)))
34. 0.000 0.000 ↓ 0.0

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord od (cost=0.56..0.78 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((lockid = (activi_people.lockid)::text) AND (userid = (activi_people.userid)::text))
35. 0.000 0.000 ↓ 0.0

CTE Scan on bucket (cost=0.00..20.00 rows=1,000 width=128) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=17,234.37..17,239.89 rows=2,207 width=517) (actual rows= loops=)

  • Sort Key: uk.lockid, uk.useraccount
37. 0.000 0.000 ↓ 0.0

Subquery Scan on uk (cost=10,176.91..17,111.79 rows=2,207 width=517) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10,176.91..17,089.72 rows=2,207 width=991) (actual rows= loops=)

  • 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))
39.          

CTE all_key

40. 0.000 0.000 ↓ 0.0

Append (cost=0.00..10,073.76 rows=226,254 width=232) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on lockkeyinfo lk_1 (cost=0.00..7,454.04 rows=121,759 width=238) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

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

43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..6,879.47 rows=2,229 width=278) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

CTE Scan on all_key lk (cost=0.00..5,656.35 rows=2,229 width=156) (actual rows= loops=)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
45. 0.000 0.000 ↓ 0.0

Index Scan using "pk_appuseracc_1570385636_97 " on appuseraccount user_info (cost=0.42..0.55 rows=1 width=122) (actual rows= loops=)

  • Index Cond: ((account)::text = (lk.useraccount)::text)
46. 0.000 0.000 ↓ 0.0

Hash (cost=49.25..49.25 rows=3,565 width=280) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on bluekeyuserinfo bk (cost=0.00..49.25 rows=3,565 width=280) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Index Scan using "pk_lockinfo_1570385706_137 " on lockinfo li (cost=0.41..0.51 rows=1 width=51) (actual rows= loops=)

  • Index Cond: (((lockid)::text = (uk.lockid)::text) AND (lockid IS NOT NULL))
49. 0.000 0.000 ↓ 0.0

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo hi (cost=0.42..0.45 rows=1 width=125) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (li.houseid)::text)
50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

Index Scan using "管理单位代码_pkey" on "管理单位代码" gl (cost=0.28..0.30 rows=1 width=90) (actual rows= loops=)

  • Index Cond: (id = ys."管理单位代码_id")
53. 0.000 0.000 ↓ 0.0

Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel (cost=0.42..0.62 rows=1 width=38) (actual rows= loops=)

  • Index Cond: ((lockid = (uk.lockid)::text) AND (userid = (uk.useraccount)::text))