#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Append (cost=37,811.65..127,875.71 rows=16,298 width=1,640) (actual rows= loops=)
2.	0.000	0.000	↓ 0.0	→ Subquery Scan on SELECT 1 (cost=37,811.65..38,608.34 rows=15,175 width=937) (actual rows= loops=)
3.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=37,811.65..38,456.59 rows=15,175 width=937) (actual rows= loops=) Group 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, '长租房'::text, 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
4.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=16,372.27..36,977.03 rows=15,175 width=937) (actual rows= loops=)
5.	0.000	0.000	↓ 0.0	→ Hash Join (cost=16,371.85..28,538.22 rows=15,175 width=898) (actual rows= loops=) Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
6.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=13,084.56..22,083.22 rows=219,343 width=955) (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))
7.				CTE all_key
8.	0.000	0.000	↓ 0.0	→ Append (cost=0.00..9,984.76 rows=224,910 width=232) (actual rows= loops=)
9.	0.000	0.000	↓ 0.0	→ Seq Scan on lockkeyinfo lk_2 (cost=0.00..7,371.76 rows=120,415 width=238) (actual rows= loops=)
10.	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=)
11.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=2,998.38..8,640.45 rows=221,553 width=522) (actual rows= loops=) Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
12.	0.000	0.000	↓ 0.0	→ CTE Scan on all_key lk (cost=0.00..5,060.47 rows=221,553 width=398) (actual rows= loops=) Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
13.	0.000	0.000	↓ 0.0	→ Hash (cost=1,703.50..1,703.50 rows=103,590 width=124) (actual rows= loops=)
14.	0.000	0.000	↓ 0.0	→ Seq Scan on appuseraccount user_info (cost=0.00..1,703.50 rows=103,590 width=124) (actual rows= loops=)
15.	0.000	0.000	↓ 0.0	→ Hash (cost=48.73..48.73 rows=3,513 width=280) (actual rows= loops=)
16.	0.000	0.000	↓ 0.0	→ Seq Scan on bluekeyuserinfo bk (cost=0.00..48.73 rows=3,513 width=280) (actual rows= loops=)
17.	0.000	0.000	↓ 0.0	→ Hash (cost=3,246.16..3,246.16 rows=3,290 width=235) (actual rows= loops=)
18.	0.000	0.000	↓ 0.0	→ Gather (cost=2,341.27..3,246.16 rows=3,290 width=235) (actual rows= loops=) Workers Planned: 1
19.	0.000	0.000	↓ 0.0	→ Parallel Hash Join (cost=1,341.27..1,917.16 rows=1,935 width=235) (actual rows= loops=) Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
20.	0.000	0.000	↓ 0.0	→ Parallel Seq Scan on lockinfo li (cost=0.00..462.93 rows=27,973 width=51) (actual rows= loops=) Filter: (lockid IS NOT NULL)
21.	0.000	0.000	↓ 0.0	→ Parallel Hash (cost=1,316.20..1,316.20 rows=2,006 width=249) (actual rows= loops=)
22.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=102.26..1,316.20 rows=2,006 width=249) (actual rows= loops=) 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) AND (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))
23.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=73.01..1,209.55 rows=28,950 width=191) (actual rows= loops=) Hash Cond: ((hi.xzqhdm)::text = xz."代码")
24.	0.000	0.000	↓ 0.0	→ Parallel Seq Scan on houseinfo hi (cost=0.00..740.60 rows=28,950 width=143) (actual rows= loops=)
25.	0.000	0.000	↓ 0.0	→ Hash (cost=53.53..53.53 rows=1,559 width=60) (actual rows= loops=)
26.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual rows= loops=) Hash Cond: (ys."行政区划_id" = xz.id)
27.	0.000	0.000	↓ 0.0	→ Seq Scan on "行政区划与管理单位代码映射" ys (cost=0.00..13.11 rows=1,191 width=32) (actual rows= loops=)
28.	0.000	0.000	↓ 0.0	→ Hash (cost=17.79..17.79 rows=1,559 width=60) (actual rows= loops=)
29.	0.000	0.000	↓ 0.0	→ Seq Scan on "行政区划代码" xz (cost=0.00..17.79 rows=1,559 width=60) (actual rows= loops=)
30.	0.000	0.000	↓ 0.0	→ Hash (cost=14.22..14.22 rows=1,202 width=90) (actual rows= loops=)
31.	0.000	0.000	↓ 0.0	→ Seq Scan on "管理单位代码" gl (cost=0.00..14.22 rows=1,202 width=90) (actual rows= loops=)
32.	0.000	0.000	↓ 0.0	→ Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel (cost=0.42..0.52 rows=1 width=46) (actual rows= loops=) Index Cond: (((li.lockid)::text = lockid) AND (userid = (lk.useraccount)::text))
33.	0.000	0.000	↓ 0.0	→ Subquery Scan on SELECT 2 (cost=89,112.89..89,185.88 rows=1,123 width=895) (actual rows= loops=)
34.	0.000	0.000	↓ 0.0	→ Unique (cost=89,112.89..89,174.65 rows=1,123 width=895) (actual rows= loops=)
35.	0.000	0.000	↓ 0.0	→ Sort (cost=89,112.89..89,115.70 rows=1,123 width=895) (actual rows= loops=) Sort Key: (COALESCE(user_info_1.headpicture, bk_1.certfrontpic, bk_1.certbackpic)), (COALESCE(user_info_1.username, bk_1.username)), (COALESCE(bk_1.certid, user_info_1.userid)), user_info_1.authstate, (COALESCE(user_info_1.account, bk_1.keyholder)), lk_1.authtime, (CASE WHEN (lk_1.valid_state = '1'::text) THEN lk_1.stoptime WHEN (lk_1.valid_state = '0'::text) THEN lk_1.expireddate ELSE NULL::character varying END), lk_1.valid_state, lk_1.authaccount, lk_1.keytype, hi_1.detailaddr, hi_1.housenumber, xz_1.""全称"", hi_1.xzqhdm, (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), (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), (CASE WHEN ((hi_1.housecategory)::text = '0'::text) THEN '网约房'::text WHEN ((hi_1.housecategory)::text = '2'::text) THEN '企业宿舍'::text ELSE NULL::text END), li_1.lockid, (count(DISTINCT opendoorrecord.opentime)), lk_1.useraccount, (max((opendoorrecord.opentime)::text))
36.	0.000	0.000	↓ 0.0	→ Merge Left Join (cost=79,600.36..89,055.99 rows=1,123 width=895) (actual rows= loops=) Merge Cond: (((li_1.lockid)::text = (opendoorrecord.lockid)::text) AND ((lk_1.useraccount)::text = (opendoorrecord.userid)::text))
37.	0.000	0.000	↓ 0.0	→ Sort (cost=2,354.44..2,357.25 rows=1,123 width=853) (actual rows= loops=) Sort Key: li_1.lockid, lk_1.useraccount
38.	0.000	0.000	↓ 0.0	→ Hash Join (cost=1,398.39..2,297.54 rows=1,123 width=853) (actual rows= loops=) Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
39.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=1,106.37..1,721.63 rows=19,830 width=932) (actual rows= loops=) Hash Cond: (((lk_1.lockid)::text = (bk_1.lockid)::text) AND ((lk_1.useraccount)::text = (bk_1.bluekeyaccount)::text)) Filter: ((COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL) AND (COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL))
40.				CTE all_key
41.	0.000	0.000	↓ 0.0	→ Append (cost=0.00..882.05 rows=20,333 width=233) (actual rows= loops=)
42.	0.000	0.000	↓ 0.0	→ Seq Scan on lockkeyinfo lk_3 (cost=0.00..640.44 rows=10,218 width=239) (actual rows= loops=)
43.	0.000	0.000	↓ 0.0	→ Seq Scan on keyhistoryinfo khi_1 (cost=0.00..139.95 rows=10,115 width=226) (actual rows= loops=)
44.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=222.12..732.21 rows=20,030 width=518) (actual rows= loops=) Hash Cond: ((lk_1.useraccount)::text = (user_info_1.account)::text)
45.	0.000	0.000	↓ 0.0	→ CTE Scan on all_key lk_1 (cost=0.00..457.49 rows=20,030 width=398) (actual rows= loops=) Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
46.	0.000	0.000	↓ 0.0	→ Hash (cost=117.94..117.94 rows=8,334 width=120) (actual rows= loops=)
47.	0.000	0.000	↓ 0.0	→ Seq Scan on appuseraccount user_info_1 (cost=0.00..117.94 rows=8,334 width=120) (actual rows= loops=)
48.	0.000	0.000	↓ 0.0	→ Hash (cost=1.06..1.06 rows=76 width=367) (actual rows= loops=)
49.	0.000	0.000	↓ 0.0	→ Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..1.06 rows=76 width=367) (actual rows= loops=)
50.	0.000	0.000	↓ 0.0	→ Hash (cost=289.37..289.37 rows=212 width=189) (actual rows= loops=)
51.	0.000	0.000	↓ 0.0	→ Hash Join (cost=223.56..289.37 rows=212 width=189) (actual rows= loops=) Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
52.	0.000	0.000	↓ 0.0	→ Seq Scan on lockinfo li_1 (cost=0.00..49.65 rows=3,745 width=41) (actual rows= loops=) Filter: (lockid IS NOT NULL)
53.	0.000	0.000	↓ 0.0	→ Hash (cost=220.20..220.20 rows=269 width=203) (actual rows= loops=)
54.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=102.26..220.20 rows=269 width=203) (actual rows= loops=) 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 IS NOT NULL) AND (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 IS NOT NULL) AND (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 = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))
55.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=73.01..180.58 rows=3,885 width=145) (actual rows= loops=) Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
56.	0.000	0.000	↓ 0.0	→ Seq Scan on houseinfo hi_1 (cost=0.00..80.74 rows=3,885 width=97) (actual rows= loops=) Filter: ((housecategory)::text = ANY ('{0,2}'::text[]))
57.	0.000	0.000	↓ 0.0	→ Hash (cost=53.53..53.53 rows=1,559 width=60) (actual rows= loops=)
58.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=37.28..53.53 rows=1,559 width=60) (actual rows= loops=) Hash Cond: (ys_1."行政区划_id" = xz_1.id)
59.	0.000	0.000	↓ 0.0	→ Seq Scan on "行政区划与管理单位代码映射" ys_1 (cost=0.00..13.11 rows=1,191 width=32) (actual rows= loops=)
60.	0.000	0.000	↓ 0.0	→ Hash (cost=17.79..17.79 rows=1,559 width=60) (actual rows= loops=)
61.	0.000	0.000	↓ 0.0	→ Seq Scan on "行政区划代码" xz_1 (cost=0.00..17.79 rows=1,559 width=60) (actual rows= loops=)
62.	0.000	0.000	↓ 0.0	→ Hash (cost=14.22..14.22 rows=1,202 width=90) (actual rows= loops=)
63.	0.000	0.000	↓ 0.0	→ Seq Scan on "管理单位代码" gl_1 (cost=0.00..14.22 rows=1,202 width=90) (actual rows= loops=)
64.	0.000	0.000	↓ 0.0	→ GroupAggregate (cost=77,245.92..85,688.64 rows=64,655 width=103) (actual rows= loops=) Group Key: opendoorrecord.lockid, opendoorrecord.userid
65.	0.000	0.000	↓ 0.0	→ Sort (cost=77,245.92..78,805.15 rows=623,694 width=51) (actual rows= loops=) Sort Key: opendoorrecord.lockid, opendoorrecord.userid
66.	0.000	0.000	↓ 0.0	→ Seq Scan on opendoorrecord (cost=0.00..17,213.88 rows=623,694 width=51) (actual rows= loops=) Filter: (((opentime)::text >= '2018-04-25'::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))

Append  (cost=37811.65..127875.71 rows=16298 width=1640)
  ->  Subquery Scan on "*SELECT* 1"  (cost=37811.65..38608.34 rows=15175 width=937)
        ->  HashAggregate  (cost=37811.65..38456.59 rows=15175 width=937)
"              Group 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, '长租房'::text, 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"
              ->  Nested Loop Left Join  (cost=16372.27..36977.03 rows=15175 width=937)
                    ->  Hash Join  (cost=16371.85..28538.22 rows=15175 width=898)
                          Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
                          ->  Hash Left Join  (cost=13084.56..22083.22 rows=219343 width=955)
                                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))"
                                CTE all_key
                                  ->  Append  (cost=0.00..9984.76 rows=224910 width=232)
                                        ->  Seq Scan on lockkeyinfo lk_2  (cost=0.00..7371.76 rows=120415 width=238)
                                        ->  Seq Scan on keyhistoryinfo khi  (cost=0.00..1488.45 rows=104495 width=225)
                                ->  Hash Left Join  (cost=2998.38..8640.45 rows=221553 width=522)
                                      Hash Cond: ((lk.useraccount)::text = (user_info.account)::text)
                                      ->  CTE Scan on all_key lk  (cost=0.00..5060.47 rows=221553 width=398)
                                            Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
                                      ->  Hash  (cost=1703.50..1703.50 rows=103590 width=124)
                                            ->  Seq Scan on appuseraccount user_info  (cost=0.00..1703.50 rows=103590 width=124)
                                ->  Hash  (cost=48.73..48.73 rows=3513 width=280)
                                      ->  Seq Scan on bluekeyuserinfo bk  (cost=0.00..48.73 rows=3513 width=280)
                          ->  Hash  (cost=3246.16..3246.16 rows=3290 width=235)
                                ->  Gather  (cost=2341.27..3246.16 rows=3290 width=235)
                                      Workers Planned: 1
                                      ->  Parallel Hash Join  (cost=1341.27..1917.16 rows=1935 width=235)
                                            Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
                                            ->  Parallel Seq Scan on lockinfo li  (cost=0.00..462.93 rows=27973 width=51)
                                                  Filter: (lockid IS NOT NULL)
                                            ->  Parallel Hash  (cost=1316.20..1316.20 rows=2006 width=249)
                                                  ->  Hash Left Join  (cost=102.26..1316.20 rows=2006 width=249)
                                                        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) AND (CASE WHEN (length((hi.xzqhdm)::text) = 9) THEN gl.""代码"" WHEN (length((hi.xzqhdm)::text) = 12) THEN gl.""上级单位代码"" ELSE NULL::text END = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))"
                                                        ->  Hash Left Join  (cost=73.01..1209.55 rows=28950 width=191)
                                                              Hash Cond: ((hi.xzqhdm)::text = xz."代码")
                                                              ->  Parallel Seq Scan on houseinfo hi  (cost=0.00..740.60 rows=28950 width=143)
                                                              ->  Hash  (cost=53.53..53.53 rows=1559 width=60)
                                                                    ->  Hash Right Join  (cost=37.28..53.53 rows=1559 width=60)
                                                                          Hash Cond: (ys."行政区划_id" = xz.id)
                                                                          ->  Seq Scan on "行政区划与管理单位代码映射" ys  (cost=0.00..13.11 rows=1191 width=32)
                                                                          ->  Hash  (cost=17.79..17.79 rows=1559 width=60)
                                                                                ->  Seq Scan on "行政区划代码" xz  (cost=0.00..17.79 rows=1559 width=60)
                                                        ->  Hash  (cost=14.22..14.22 rows=1202 width=90)
                                                              ->  Seq Scan on "管理单位代码" gl  (cost=0.00..14.22 rows=1202 width=90)
                    ->  Index Scan using opendoor_summary_mrel_lockid_userid_idx on opendoor_summary_mrel  (cost=0.42..0.52 rows=1 width=46)
                          Index Cond: (((li.lockid)::text = lockid) AND (userid = (lk.useraccount)::text))
  ->  Subquery Scan on "*SELECT* 2"  (cost=89112.89..89185.88 rows=1123 width=895)
        ->  Unique  (cost=89112.89..89174.65 rows=1123 width=895)
              ->  Sort  (cost=89112.89..89115.70 rows=1123 width=895)
"                    Sort Key: (COALESCE(user_info_1.headpicture, bk_1.certfrontpic, bk_1.certbackpic)), (COALESCE(user_info_1.username, bk_1.username)), (COALESCE(bk_1.certid, user_info_1.userid)), user_info_1.authstate, (COALESCE(user_info_1.account, bk_1.keyholder)), lk_1.authtime, (CASE WHEN (lk_1.valid_state = '1'::text) THEN lk_1.stoptime WHEN (lk_1.valid_state = '0'::text) THEN lk_1.expireddate ELSE NULL::character varying END), lk_1.valid_state, lk_1.authaccount, lk_1.keytype, hi_1.detailaddr, hi_1.housenumber, xz_1.""全称"", hi_1.xzqhdm, (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), (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), (CASE WHEN ((hi_1.housecategory)::text = '0'::text) THEN '网约房'::text WHEN ((hi_1.housecategory)::text = '2'::text) THEN '企业宿舍'::text ELSE NULL::text END), li_1.lockid, (count(DISTINCT opendoorrecord.opentime)), lk_1.useraccount, (max((opendoorrecord.opentime)::text))"
                    ->  Merge Left Join  (cost=79600.36..89055.99 rows=1123 width=895)
                          Merge Cond: (((li_1.lockid)::text = (opendoorrecord.lockid)::text) AND ((lk_1.useraccount)::text = (opendoorrecord.userid)::text))
                          ->  Sort  (cost=2354.44..2357.25 rows=1123 width=853)
"                                Sort Key: li_1.lockid, lk_1.useraccount"
                                ->  Hash Join  (cost=1398.39..2297.54 rows=1123 width=853)
                                      Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
                                      ->  Hash Left Join  (cost=1106.37..1721.63 rows=19830 width=932)
                                            Hash Cond: (((lk_1.lockid)::text = (bk_1.lockid)::text) AND ((lk_1.useraccount)::text = (bk_1.bluekeyaccount)::text))
"                                            Filter: ((COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL) AND (COALESCE(bk_1.certid, user_info_1.userid, user_info_1.account) IS NOT NULL))"
                                            CTE all_key
                                              ->  Append  (cost=0.00..882.05 rows=20333 width=233)
                                                    ->  Seq Scan on lockkeyinfo lk_3  (cost=0.00..640.44 rows=10218 width=239)
                                                    ->  Seq Scan on keyhistoryinfo khi_1  (cost=0.00..139.95 rows=10115 width=226)
                                            ->  Hash Left Join  (cost=222.12..732.21 rows=20030 width=518)
                                                  Hash Cond: ((lk_1.useraccount)::text = (user_info_1.account)::text)
                                                  ->  CTE Scan on all_key lk_1  (cost=0.00..457.49 rows=20030 width=398)
                                                        Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (userrole <> 255))
                                                  ->  Hash  (cost=117.94..117.94 rows=8334 width=120)
                                                        ->  Seq Scan on appuseraccount user_info_1  (cost=0.00..117.94 rows=8334 width=120)
                                            ->  Hash  (cost=1.06..1.06 rows=76 width=367)
                                                  ->  Seq Scan on bluekeyuserinfo bk_1  (cost=0.00..1.06 rows=76 width=367)
                                      ->  Hash  (cost=289.37..289.37 rows=212 width=189)
                                            ->  Hash Join  (cost=223.56..289.37 rows=212 width=189)
                                                  Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
                                                  ->  Seq Scan on lockinfo li_1  (cost=0.00..49.65 rows=3745 width=41)
                                                        Filter: (lockid IS NOT NULL)
                                                  ->  Hash  (cost=220.20..220.20 rows=269 width=203)
                                                        ->  Hash Left Join  (cost=102.26..220.20 rows=269 width=203)
                                                              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 IS NOT NULL) AND (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 IS NOT NULL) AND (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 = ANY ('{330522630000,330522550000,330522660000,330522700000,330522580000,330522500000,330522590000,330522690000,330522600000,330522650000,330522530000,330522990000,330522620000,330522520000}'::text[])))"
                                                              ->  Hash Left Join  (cost=73.01..180.58 rows=3885 width=145)
                                                                    Hash Cond: ((hi_1.xzqhdm)::text = xz_1."代码")
                                                                    ->  Seq Scan on houseinfo hi_1  (cost=0.00..80.74 rows=3885 width=97)
"                                                                          Filter: ((housecategory)::text = ANY ('{0,2}'::text[]))"
                                                                    ->  Hash  (cost=53.53..53.53 rows=1559 width=60)
                                                                          ->  Hash Right Join  (cost=37.28..53.53 rows=1559 width=60)
                                                                                Hash Cond: (ys_1."行政区划_id" = xz_1.id)
                                                                                ->  Seq Scan on "行政区划与管理单位代码映射" ys_1  (cost=0.00..13.11 rows=1191 width=32)
                                                                                ->  Hash  (cost=17.79..17.79 rows=1559 width=60)
                                                                                      ->  Seq Scan on "行政区划代码" xz_1  (cost=0.00..17.79 rows=1559 width=60)
                                                              ->  Hash  (cost=14.22..14.22 rows=1202 width=90)
                                                                    ->  Seq Scan on "管理单位代码" gl_1  (cost=0.00..14.22 rows=1202 width=90)
                          ->  GroupAggregate  (cost=77245.92..85688.64 rows=64655 width=103)
"                                Group Key: opendoorrecord.lockid, opendoorrecord.userid"
                                ->  Sort  (cost=77245.92..78805.15 rows=623694 width=51)
"                                      Sort Key: opendoorrecord.lockid, opendoorrecord.userid"
                                      ->  Seq Scan on opendoorrecord  (cost=0.00..17213.88 rows=623694 width=51)
"                                            Filter: (((opentime)::text >= '2018-04-25'::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))"

Per node type stats

node type	count	sum of times	% of query
Append	3	0.000 ms	0.0 %
CTE Scan	2	0.000 ms	0.0 %
Gather	1	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	13	0.000 ms	0.0 %
Hash Join	3	0.000 ms	0.0 %
Hash Left Join	8	0.000 ms	0.0 %
Hash Right Join	2	0.000 ms	0.0 %
HashAggregate	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
Merge Left Join	1	0.000 ms	0.0 %
Nested Loop Left Join	1	0.000 ms	0.0 %
Parallel Hash	1	0.000 ms	0.0 %
Parallel Hash Join	1	0.000 ms	0.0 %
Parallel Seq Scan	2	0.000 ms	0.0 %
Seq Scan	17	0.000 ms	0.0 %
Sort	3	0.000 ms	0.0 %
Subquery Scan	2	0.000 ms	0.0 %
Unique	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
"管理单位代码"	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
"行政区划与管理单位代码映射"	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
"行政区划代码"	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
appuseraccount	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
bluekeyuserinfo	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
houseinfo	2	0.000 ms	0.0 %
Parallel Seq Scan	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
keyhistoryinfo	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
lockinfo	2	0.000 ms	0.0 %
Parallel Seq Scan	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
lockkeyinfo	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
opendoor_summary_mrel	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
opendoorrecord	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 16lg

Color mode:

Visible columns:

Per node type stats

Per table stats