explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 86Eo

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,234.608 ↑ 6.0 1 1

Subquery Scan on app钥匙使用人基本信息 (cost=127,837.63..127,838.41 rows=6 width=1,098) (actual time=3,234.606..3,234.608 rows=1 loops=1)

2. 0.023 3,234.605 ↑ 6.0 1 1

GroupAggregate (cost=127,837.63..127,838.35 rows=6 width=1,130) (actual time=3,234.604..3,234.605 rows=1 loops=1)

  • Group Key: app_user_house.account, app_user_house.username, app_user_house.userid, app_user_house.headpicture, app_user_house.createtime, app_user_house.authstate, app_user_house.lockid, app_user_house.lock_count, app_user_house.house_info, app_user_house.gldwdm, app_user_house.xzqhdm
3.          

CTE app_user_house

4. 114.502 2,992.793 ↓ 3.5 4,003 1

GroupAggregate (cost=95,150.45..95,204.98 rows=1,148 width=239) (actual time=2,875.713..2,992.793 rows=4,003 loops=1)

  • Group Key: ac.account, ac.username, ac.headpicture, ac.userid, ac.createtime, ac.authstate, gl."代码", hi_1.xzqhdm
5. 16.409 2,878.291 ↓ 4.0 4,546 1

Sort (cost=95,150.45..95,153.32 rows=1,148 width=332) (actual time=2,875.648..2,878.291 rows=4,546 loops=1)

  • Sort Key: ac.account, ac.username, ac.headpicture, ac.userid, ac.createtime, ac.authstate, gl."代码", hi_1.xzqhdm
  • Sort Method: quicksort Memory: 2147kB
6. 9.814 2,861.882 ↓ 4.0 4,546 1

Hash Join (cost=2,271.74..95,092.10 rows=1,148 width=332) (actual time=64.987..2,861.882 rows=4,546 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = rtrim(xz."代码", '0'::text))
7. 14.703 2,848.088 ↓ 8.3 8,701 1

Hash Join (cost=2,240.85..94,968.89 rows=1,043 width=317) (actual time=60.997..2,848.088 rows=8,701 loops=1)

  • Hash Cond: ((lk.useraccount)::text = (ac.account)::text)
8. 19.366 2,818.495 ↓ 8.5 8,870 1

Nested Loop (cost=1,112.86..93,838.16 rows=1,043 width=187) (actual time=46.079..2,818.495 rows=8,870 loops=1)

9. 15.879 2,763.501 ↓ 8.4 8,907 1

Merge Join (cost=1,112.57..93,039.48 rows=1,062 width=141) (actual time=46.064..2,763.501 rows=8,907 loops=1)

  • Merge Cond: ((lk.lockid)::text = (li_1.lockid)::text)
10. 24.922 2,736.788 ↓ 8.4 8,907 1

Merge Left Join (cost=1,112.29..92,131.86 rows=1,062 width=111) (actual time=46.054..2,736.788 rows=8,907 loops=1)

  • Merge Cond: (((lk.lockid)::text = (opendoorrecord_1.lockid)::text) AND ((lk.useraccount)::text = (opendoorrecord_1.userid)::text))
11. 36.974 51.233 ↓ 8.4 8,907 1

Sort (cost=1,111.74..1,114.39 rows=1,062 width=71) (actual time=45.765..51.233 rows=8,907 loops=1)

  • Sort Key: lk.lockid, lk.useraccount
  • Sort Method: quicksort Memory: 1637kB
12. 14.259 14.259 ↓ 8.4 8,907 1

Seq Scan on lockkeyinfo lk (cost=0.00..1,058.36 rows=1,062 width=71) (actual time=0.016..14.259 rows=8,907 loops=1)

  • Filter: ((userrole <> 255) AND ((authtime)::text <= (expireddate)::text) AND (keytype = 0) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 6505
13. 1,369.815 2,660.633 ↑ 6.1 18,291 1

GroupAggregate (cost=0.55..89,346.95 rows=111,012 width=72) (actual time=0.060..2,660.633 rows=18,291 loops=1)

  • Group Key: opendoorrecord_1.lockid, opendoorrecord_1.userid
14. 1,290.818 1,290.818 ↓ 3.0 1,132,267 1

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.55..84,471.12 rows=376,571 width=52) (actual time=0.040..1,290.818 rows=1,132,267 loops=1)

  • Filter: ((opentime)::date >= '2018-04-25'::date)
  • Rows Removed by Filter: 94225
  • Heap Fetches: 96817
15. 10.834 10.834 ↓ 1.0 4,965 1

Index Scan using lockinfo_pk on lockinfo li_1 (cost=0.28..881.94 rows=4,964 width=49) (actual time=0.007..10.834 rows=4,965 loops=1)

16. 35.628 35.628 ↑ 1.0 1 8,907

Index Scan using houseinfo_pk on houseinfo hi_1 (cost=0.28..0.75 rows=1 width=109) (actual time=0.004..0.004 rows=1 loops=8,907)

  • Index Cond: ((houseid)::text = (li_1.houseid)::text)
17. 6.663 14.890 ↑ 1.0 10,844 1

Hash (cost=992.44..992.44 rows=10,844 width=142) (actual time=14.890..14.890 rows=10,844 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1662kB
18. 8.227 8.227 ↑ 1.0 10,844 1

Seq Scan on appuseraccount ac (cost=0.00..992.44 rows=10,844 width=142) (actual time=0.005..8.227 rows=10,844 loops=1)

19. 0.238 3.980 ↓ 1.0 304 1

Hash (cost=27.18..27.18 rows=297 width=28) (actual time=3.980..3.980 rows=304 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
20. 0.280 3.742 ↓ 1.0 304 1

Hash Right Join (cost=20.50..27.18 rows=297 width=28) (actual time=2.876..3.742 rows=304 loops=1)

  • Hash Cond: (ys."行政区划_id" = xz.id)
21. 0.298 2.562 ↓ 1.3 304 1

Hash Left Join (cost=10.82..16.86 rows=240 width=31) (actual time=1.970..2.562 rows=304 loops=1)

  • Hash Cond: (ys."管理单位代码_id" = gl.id)
22. 0.833 0.833 ↓ 1.3 304 1

Seq Scan on "行政区划与管理单位代码映射" ys (cost=0.00..5.40 rows=240 width=32) (actual time=0.530..0.833 rows=304 loops=1)

23. 0.166 1.431 ↑ 1.0 303 1

Hash (cost=7.03..7.03 rows=303 width=31) (actual time=1.431..1.431 rows=303 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
24. 1.265 1.265 ↑ 1.0 303 1

Seq Scan on "管理单位代码" gl (cost=0.00..7.03 rows=303 width=31) (actual time=0.603..1.265 rows=303 loops=1)

25. 0.155 0.900 ↓ 1.0 302 1

Hash (cost=5.97..5.97 rows=297 width=29) (actual time=0.900..0.900 rows=302 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
26. 0.745 0.745 ↓ 1.0 302 1

Seq Scan on "行政区划代码" xz (cost=0.00..5.97 rows=297 width=29) (actual time=0.009..0.745 rows=302 loops=1)

  • Filter: ("代码" IS NOT NULL)
27. 0.017 3,234.582 ↑ 18.0 1 1

Sort (cost=32,632.65..32,632.70 rows=18 width=1,309) (actual time=3,234.581..3,234.582 rows=1 loops=1)

  • Sort Key: app_user_house.username, app_user_house.userid, app_user_house.headpicture, app_user_house.createtime, app_user_house.authstate, app_user_house.lockid, app_user_house.lock_count, app_user_house.house_info, app_user_house.gldwdm, app_user_house.xzqhdm
  • Sort Method: quicksort Memory: 26kB
28. 0.015 3,234.565 ↑ 18.0 1 1

Hash Left Join (cost=32,567.91..32,632.28 rows=18 width=1,309) (actual time=3,110.380..3,234.565 rows=1 loops=1)

  • Hash Cond: (((lockkeyinfo.useraccount)::text = (odr.userid)::text) AND ((lockkeyinfo.lockid)::text = (odr.lockid)::text))
29. 0.006 3,012.599 ↑ 18.0 1 1

Nested Loop Left Join (cost=2,150.21..2,214.48 rows=18 width=1,359) (actual time=2,888.415..3,012.599 rows=1 loops=1)

  • Join Filter: ((hi.xzqhdm)::text = (app_user_house.xzqhdm)::text)
30. 0.006 3,012.588 ↑ 18.0 1 1

Nested Loop Left Join (cost=2,149.93..2,200.72 rows=18 width=1,334) (actual time=2,888.405..3,012.588 rows=1 loops=1)

  • Join Filter: ((lockkeyinfo.useraccount)::text = (app_user_house.account)::text)
31. 2,999.933 2,999.933 ↑ 6.0 1 1

CTE Scan on app_user_house (cost=0.00..25.83 rows=6 width=1,000) (actual time=2,875.753..2,999.933 rows=1 loops=1)

  • Filter: ((account)::text = '13002616410'::text)
  • Rows Removed by Filter: 4002
32. 0.002 12.649 ↓ 0.0 0 1

Materialize (cost=2,149.93..2,174.63 rows=3 width=334) (actual time=12.649..12.649 rows=0 loops=1)

33. 0.002 12.647 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,149.93..2,174.61 rows=3 width=334) (actual time=12.647..12.647 rows=0 loops=1)

34. 0.001 12.645 ↓ 0.0 0 1

HashAggregate (cost=2,149.65..2,149.68 rows=3 width=406) (actual time=12.645..12.645 rows=0 loops=1)

  • Group Key: lockkeyinfo.keyid, lockkeyinfo.lockid, lockkeyinfo.useraccount, lockkeyinfo.authtime, lockkeyinfo.keytype, lockkeyinfo.expireddate, (NULL::text)
35. 0.003 12.644 ↓ 0.0 0 1

Append (cost=0.00..2,149.60 rows=3 width=406) (actual time=12.643..12.644 rows=0 loops=1)

36. 6.850 6.850 ↓ 0.0 0 1

Seq Scan on lockkeyinfo (cost=0.00..1,058.36 rows=1 width=140) (actual time=6.850..6.850 rows=0 loops=1)

  • Filter: ((userrole <> 255) AND ((useraccount)::text = '13002616410'::text) AND ((((expireddate)::date < CURRENT_DATE) AND (keytype = 0)) OR (keystate = 1)))
  • Rows Removed by Filter: 15412
37. 0.002 5.791 ↓ 0.0 0 1

GroupAggregate (cost=1,091.14..1,091.21 rows=2 width=141) (actual time=5.791..5.791 rows=0 loops=1)

  • Group Key: keyhistoryinfo.keyid, keyhistoryinfo.lockid, keyhistoryinfo.useraccount, keyhistoryinfo.authtime, keyhistoryinfo.keytype, keyhistoryinfo.expireddate
38. 0.009 5.789 ↓ 0.0 0 1

Sort (cost=1,091.14..1,091.15 rows=2 width=129) (actual time=5.789..5.789 rows=0 loops=1)

  • Sort Key: keyhistoryinfo.keyid, keyhistoryinfo.lockid, keyhistoryinfo.authtime, keyhistoryinfo.expireddate
  • Sort Method: quicksort Memory: 25kB
39. 5.780 5.780 ↓ 0.0 0 1

Seq Scan on keyhistoryinfo (cost=0.00..1,091.13 rows=2 width=129) (actual time=5.780..5.780 rows=0 loops=1)

  • Filter: ((userrole <> 255) AND (keytype = 0) AND ((useraccount)::text = '13002616410'::text))
  • Rows Removed by Filter: 21322
40. 0.000 0.000 ↓ 0.0 0

Index Scan using lockinfo_pk on lockinfo li (cost=0.28..8.30 rows=1 width=49) (never executed)

  • Index Cond: ((lockid)::text = (lockkeyinfo.lockid)::text)
41. 0.005 0.005 ↓ 0.0 0 1

Index Scan using houseinfo_pk on houseinfo hi (cost=0.28..0.75 rows=1 width=98) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((houseid)::text = (li.houseid)::text)
42. 0.005 221.951 ↑ 47.0 1 1

Hash (cost=30,417.00..30,417.00 rows=47 width=40) (actual time=221.951..221.951 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.015 221.946 ↑ 47.0 1 1

Subquery Scan on odr (cost=30,410.74..30,417.00 rows=47 width=40) (actual time=221.944..221.946 rows=1 loops=1)

44. 0.000 221.931 ↑ 47.0 1 1

Finalize GroupAggregate (cost=30,410.74..30,416.53 rows=47 width=72) (actual time=221.930..221.931 rows=1 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
45. 0.000 222.016 ↑ 13.3 3 1

Gather Merge (cost=30,410.74..30,415.76 rows=40 width=40) (actual time=221.907..222.016 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
46. 0.060 570.363 ↑ 20.0 1 3

Partial GroupAggregate (cost=29,410.72..29,411.12 rows=20 width=40) (actual time=190.121..190.121 rows=1 loops=3)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
47. 5.376 570.303 ↓ 1.4 27 3

Sort (cost=29,410.72..29,410.77 rows=20 width=52) (actual time=190.091..190.101 rows=27 loops=3)

  • Sort Key: opendoorrecord.lockid
  • Sort Method: quicksort Memory: 27kB
48. 564.927 564.927 ↓ 1.4 27 3

Parallel Seq Scan on opendoorrecord (cost=0.00..29,410.28 rows=20 width=52) (actual time=52.781..188.309 rows=27 loops=3)

  • Filter: (((userid)::text = '13002616410'::text) AND ((opentime)::date >= '2018-04-25'::date))
  • Rows Removed by Filter: 408804