explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UXcy

Settings
# exclusive inclusive rows x rows loops node
1. 0.169 3,447.099 ↓ 239.0 239 1

Unique (cost=175,942,414.94..175,942,414.99 rows=1 width=820) (actual time=3,446.907..3,447.099 rows=239 loops=1)

2.          

CTE dates_in_interest

3. 0.056 0.057 ↑ 32.3 31 1

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

4. 0.001 0.001 ↑ 1.0 1 1

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

5.          

CTE bucket

6. 0.164 0.164 ↑ 32.3 31 1

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

7.          

CTE candidates_r2

8. 16.698 82.109 ↑ 4,984.9 17,099 1

Group (cost=85,474,604.91..90,191,832.69 rows=85,236,800 width=119) (actual time=61.130..82.109 rows=17,099 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid, bucket_1.*
9. 49.189 65.411 ↑ 22,730.3 20,753 1

Sort (cost=85,474,604.91..86,653,911.85 rows=471,722,778 width=119) (actual time=61.125..65.411 rows=20,753 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid, bucket_1.*
  • Sort Method: quicksort Memory: 6281kB
10. 3.822 16.222 ↑ 22,730.3 20,753 1

Nested Loop (cost=0.43..14,198,203.50 rows=471,722,778 width=119) (actual time=0.051..16.222 rows=20,753 loops=1)

11. 0.217 0.217 ↑ 32.3 31 1

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

12. 12.183 12.183 ↑ 705.1 669 31

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

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

CTE candidates

14. 11.695 206.511 ↑ 14,454.3 5,897 1

Nested Loop (cost=0.56..70,107,268.00 rows=85,236,800 width=188) (actual time=61.196..206.511 rows=5,897 loops=1)

15. 92.222 92.222 ↑ 4,984.9 17,099 1

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

16. 102.594 102.594 ↓ 0.0 0 17,099

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.56..0.78 rows=1 width=58) (actual time=0.006..0.006 rows=0 loops=17,099)

  • 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))
  • Heap Fetches: 147
17.          

CTE activi_people

18. 10.172 223.293 ↑ 48.5 275 1

GroupAggregate (cost=13,831,524.01..14,684,392.01 rows=13,333 width=156) (actual time=212.589..223.293 rows=275 loops=1)

  • Group Key: candidates.lockid, candidates.userid
  • Filter: (count(DISTINCT candidates.bucket) >= 3)
  • Rows Removed by Filter: 1833
19. 2.824 213.121 ↑ 14,454.3 5,897 1

Sort (cost=13,831,524.01..14,044,616.01 rows=85,236,800 width=188) (actual time=212.537..213.121 rows=5,897 loops=1)

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 1759kB
20. 210.297 210.297 ↑ 14,454.3 5,897 1

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

21. 0.639 3,446.930 ↓ 284.0 284 1

Sort (cost=958,847.20..958,847.21 rows=1 width=820) (actual time=3,446.906..3,446.930 rows=284 loops=1)

  • 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
  • Sort Method: quicksort Memory: 171kB
22. 1.754 3,446.291 ↓ 284.0 284 1

Nested Loop Left Join (cost=930,694.03..958,847.19 rows=1 width=820) (actual time=3,153.662..3,446.291 rows=284 loops=1)

23. 0.364 3,426.645 ↓ 284.0 284 1

Nested Loop Left Join (cost=930,693.61..958,846.51 rows=1 width=863) (actual time=3,152.533..3,426.645 rows=284 loops=1)

  • 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: 29
24. 41.819 3,424.403 ↓ 313.0 313 1

Nested Loop Left Join (cost=930,693.33..958,846.19 rows=1 width=805) (actual time=3,152.510..3,424.403 rows=313 loops=1)

  • Join Filter: (ys."行政区划_id" = xz.id)
  • Rows Removed by Join Filter: 400356
25. 64.946 3,340.329 ↓ 313.0 313 1

Nested Loop Left Join (cost=930,693.33..958,818.20 rows=1 width=805) (actual time=3,152.375..3,340.329 rows=313 loops=1)

  • Join Filter: (xz."代码" = (hi.xzqhdm)::text)
  • Rows Removed by Join Filter: 483614
26. 0.569 3,222.799 ↓ 313.0 313 1

Nested Loop (cost=930,693.33..958,780.92 rows=1 width=773) (actual time=3,152.324..3,222.799 rows=313 loops=1)

27. 0.568 3,219.100 ↓ 313.0 313 1

Nested Loop (cost=930,692.91..958,780.47 rows=1 width=713) (actual time=3,152.301..3,219.100 rows=313 loops=1)

28. 23.555 3,209.455 ↓ 4.2 313 1

Merge Join (cost=930,692.50..958,742.41 rows=74 width=681) (actual time=3,151.830..3,209.455 rows=313 loops=1)

  • Merge Cond: (((activi_people.lockid)::text = (uk.lockid)::text) AND ((activi_people.userid)::text = (uk.useraccount)::text))
29. 9.587 1,399.055 ↑ 4.8 275 1

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

  • Group Key: activi_people.lockid, activi_people.userid
30. 5.874 1,389.468 ↑ 242.4 11,550 1

Sort (cost=913,453.20..920,453.03 rows=2,799,930 width=176) (actual time=1,388.646..1,389.468 rows=11,550 loops=1)

  • Sort Key: activi_people.lockid, activi_people.userid
  • Sort Method: quicksort Memory: 2009kB
31. 2.238 1,383.594 ↑ 242.4 11,550 1

Nested Loop (cost=0.56..599,951.67 rows=2,799,930 width=176) (actual time=212.657..1,383.594 rows=11,550 loops=1)

32. 223.606 223.606 ↑ 48.5 275 1

CTE Scan on activi_people (cost=0.00..266.66 rows=13,333 width=156) (actual time=212.590..223.606 rows=275 loops=1)

33. 497.930 1,157.750 ↑ 5.0 42 275

Nested Loop (cost=0.56..40.78 rows=210 width=20) (actual time=0.790..4.210 rows=42 loops=275)

  • 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)))
  • Rows Removed by Join Filter: 7207
34. 402.600 402.600 ↓ 234.0 234 275

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

  • Index Cond: ((lockid = (activi_people.lockid)::text) AND (userid = (activi_people.userid)::text))
  • Heap Fetches: 2174
35. 257.220 257.220 ↑ 32.3 31 64,305

CTE Scan on bucket (cost=0.00..20.00 rows=1,000 width=128) (actual time=0.000..0.004 rows=31 loops=64,305)

36. 259.883 1,786.845 ↓ 57.0 125,829 1

Sort (cost=17,239.30..17,244.81 rows=2,207 width=517) (actual time=1,762.946..1,786.845 rows=125,829 loops=1)

  • Sort Key: uk.lockid, uk.useraccount
  • Sort Method: quicksort Memory: 36232kB
37. 26.933 1,526.962 ↓ 57.9 127,826 1

Subquery Scan on uk (cost=10,180.48..17,116.72 rows=2,207 width=517) (actual time=3.311..1,526.962 rows=127,826 loops=1)

38. 186.798 1,500.029 ↓ 57.9 127,826 1

Hash Left Join (cost=10,180.48..17,094.65 rows=2,207 width=991) (actual time=3.310..1,500.029 rows=127,826 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: 10548
39.          

CTE all_key

40. 28.323 351.295 ↓ 1.0 231,607 1

Append (cost=0.00..10,077.34 rows=226,308 width=232) (actual time=0.032..351.295 rows=231,607 loops=1)

41. 268.466 268.466 ↑ 1.0 121,036 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..7,457.35 rows=121,813 width=238) (actual time=0.031..268.466 rows=121,036 loops=1)

42. 54.506 54.506 ↓ 1.1 110,571 1

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

43. 31.844 1,310.008 ↓ 62.1 138,374 1

Nested Loop Left Join (cost=0.42..6,880.82 rows=2,229 width=278) (actual time=0.067..1,310.008 rows=138,374 loops=1)

44. 586.294 586.294 ↓ 62.1 138,374 1

CTE Scan on all_key lk (cost=0.00..5,657.70 rows=2,229 width=156) (actual time=0.040..586.294 rows=138,374 loops=1)

  • Filter: ((keyid IS NOT NULL) AND (authtime IS NOT NULL) AND (keytype = ANY ('{0,2}'::integer[])) AND (userrole <> 255))
  • Rows Removed by Filter: 93233
45. 691.870 691.870 ↑ 1.0 1 138,374

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

  • Index Cond: ((account)::text = (lk.useraccount)::text)
46. 1.555 3.223 ↓ 1.0 3,576 1

Hash (cost=49.25..49.25 rows=3,565 width=280) (actual time=3.223..3.223 rows=3,576 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 883kB
47. 1.668 1.668 ↓ 1.0 3,576 1

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

48. 9.077 9.077 ↑ 1.0 1 313

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

  • Index Cond: (((lockid)::text = (uk.lockid)::text) AND (lockid IS NOT NULL))
49. 3.130 3.130 ↑ 1.0 1 313

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

  • Index Cond: ((houseid)::text = (li.houseid)::text)
50. 52.584 52.584 ↑ 1.0 1,546 313

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

51. 42.255 42.255 ↓ 1.1 1,280 313

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

52. 1.878 1.878 ↑ 1.0 1 313

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

  • Index Cond: (id = ys."管理单位代码_id")
53. 17.892 17.892 ↑ 1.0 1 284

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

  • Index Cond: ((lockid = (uk.lockid)::text) AND (userid = (uk.useraccount)::text))
Planning time : 12.176 ms
Execution time : 3,458.577 ms