explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ij3Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 389.829 ↑ 1.0 1 1

GroupAggregate (cost=64,145.99..64,146.11 rows=1 width=369) (actual time=389.828..389.829 rows=1 loops=1)

  • Group Key: ac.account, ac.username, ac.userid, ac.headpicture, ac.createtime, ac.authstate, (array_agg(DISTINCT li_1.lockid)), (count(DISTINCT li_1.lockid)), (jsonb_agg(DISTINCT jsonb_build_object('门锁编号', li_1.lockid, '地址', hi_1.detailaddr, '房间号', hi_1.housenumber, '管理单位代码', hi_1.gldwdm, '钥匙发放时间', lk.authtime, '钥匙到期时间', lk.expireddate, '最近开门时间', (max((opendoorrecord_1.opentime)::text)), '开门次数', (count(DISTINCT opendoorrecord_1.opentime))))), gl."代码", hi_1.xzqhdm
2. 0.016 389.784 ↑ 3.0 1 1

Sort (cost=64,145.99..64,146.00 rows=3 width=548) (actual time=389.783..389.784 rows=1 loops=1)

  • Sort Key: ac.username, ac.userid, ac.headpicture, ac.createtime, ac.authstate, (array_agg(DISTINCT li_1.lockid)), (count(DISTINCT li_1.lockid)), (jsonb_agg(DISTINCT jsonb_build_object('门锁编号', li_1.lockid, '地址', hi_1.detailaddr, '房间号', hi_1.housenumber, '管理单位代码', hi_1.gldwdm, '钥匙发放时间', lk.authtime, '钥匙到期时间', lk.expireddate, '最近开门时间', (max((opendoorrecord_1.opentime)::text)), '开门次数', (count(DISTINCT opendoorrecord_1.opentime))))), gl."代码", hi_1.xzqhdm
  • Sort Method: quicksort Memory: 26kB
3. 0.005 389.768 ↑ 3.0 1 1

Nested Loop Left Join (cost=64,137.31..64,145.96 rows=3 width=548) (actual time=389.763..389.768 rows=1 loops=1)

  • Join Filter: ((hi.xzqhdm)::text = (hi_1.xzqhdm)::text)
4. 0.015 389.756 ↑ 3.0 1 1

Hash Right Join (cost=64,137.03..64,143.67 rows=3 width=523) (actual time=389.752..389.756 rows=1 loops=1)

  • Hash Cond: (((opendoorrecord.userid)::text = (lockkeyinfo.useraccount)::text) AND ((opendoorrecord.lockid)::text = (lockkeyinfo.lockid)::text))
5. 0.000 184.113 ↑ 47.0 1 1

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

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
6. 0.000 186.223 ↑ 13.3 3 1

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

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.060 526.053 ↑ 20.0 1 3

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

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
8. 0.312 525.993 ↓ 1.4 27 3

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

  • Sort Key: opendoorrecord.lockid
  • Sort Method: quicksort Memory: 27kB
9. 525.681 525.681 ↓ 1.4 27 3

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

  • Filter: (((userid)::text = '13002616410'::text) AND ((opentime)::date >= '2018-04-25'::date))
  • Rows Removed by Filter: 408824
10. 0.006 205.628 ↑ 3.0 1 1

Hash (cost=33,726.25..33,726.25 rows=3 width=573) (actual time=205.627..205.628 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.004 205.622 ↑ 3.0 1 1

Nested Loop Left Join (cost=33,701.47..33,726.25 rows=3 width=573) (actual time=205.620..205.622 rows=1 loops=1)

12. 0.005 205.615 ↑ 3.0 1 1

Nested Loop Left Join (cost=33,701.19..33,701.34 rows=3 width=543) (actual time=205.613..205.615 rows=1 loops=1)

  • Join Filter: ((lockkeyinfo.useraccount)::text = (ac.account)::text)
13. 0.061 198.699 ↑ 1.0 1 1

GroupAggregate (cost=31,551.54..31,551.59 rows=1 width=239) (actual time=198.699..198.699 rows=1 loops=1)

  • Group Key: ac.account, ac.username, ac.headpicture, ac.userid, ac.createtime, ac.authstate, gl."代码", hi_1.xzqhdm
14. 0.011 198.638 ↑ 1.0 1 1

Sort (cost=31,551.54..31,551.54 rows=1 width=332) (actual time=198.637..198.638 rows=1 loops=1)

  • Sort Key: ac.username, ac.headpicture, ac.userid, ac.createtime, ac.authstate, gl."代码", hi_1.xzqhdm
  • Sort Method: quicksort Memory: 25kB
15. 0.005 198.627 ↑ 1.0 1 1

Nested Loop Left Join (cost=30,417.41..31,551.53 rows=1 width=332) (actual time=192.267..198.627 rows=1 loops=1)

16. 0.005 198.608 ↑ 1.0 1 1

Nested Loop Left Join (cost=30,417.14..31,551.10 rows=1 width=333) (actual time=192.249..198.608 rows=1 loops=1)

  • Join Filter: (((opendoorrecord_1.userid)::text = (lk.useraccount)::text) AND ((opendoorrecord_1.lockid)::text = (lk.lockid)::text))
17. 0.128 6.776 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..1,132.58 rows=1 width=324) (actual time=0.418..6.776 rows=1 loops=1)

  • Join Filter: (ys."行政区划_id" = xz.id)
  • Rows Removed by Join Filter: 303
18. 0.716 6.520 ↑ 1.0 1 1

Nested Loop (cost=0.85..1,124.18 rows=1 width=324) (actual time=0.352..6.520 rows=1 loops=1)

  • Join Filter: ((hi_1.xzqhdm)::text = rtrim(xz."代码", '0'::text))
  • Rows Removed by Join Filter: 301
19. 0.002 5.640 ↑ 1.0 1 1

Nested Loop (cost=0.85..1,113.76 rows=1 width=308) (actual time=0.272..5.640 rows=1 loops=1)

20. 0.010 5.630 ↑ 1.0 1 1

Nested Loop (cost=0.57..1,113.01 rows=1 width=262) (actual time=0.263..5.630 rows=1 loops=1)

21. 0.005 5.609 ↑ 1.0 1 1

Nested Loop (cost=0.29..1,104.70 rows=1 width=213) (actual time=0.243..5.609 rows=1 loops=1)

22. 0.015 0.015 ↑ 1.0 1 1

Index Scan using appuseraccount_pk on appuseraccount ac (cost=0.29..8.30 rows=1 width=142) (actual time=0.012..0.015 rows=1 loops=1)

  • Index Cond: ((account)::text = '13002616410'::text)
23. 5.589 5.589 ↑ 1.0 1 1

Seq Scan on lockkeyinfo lk (cost=0.00..1,096.39 rows=1 width=71) (actual time=0.228..5.589 rows=1 loops=1)

  • Filter: ((userrole <> 255) AND ((authtime)::text <= (expireddate)::text) AND ((useraccount)::text = '13002616410'::text) AND (keytype = 0) AND (keystate = 0) AND ((expireddate)::date >= CURRENT_DATE))
  • Rows Removed by Filter: 15411
24. 0.011 0.011 ↑ 1.0 1 1

Index Scan using lockinfo_pk on lockinfo li_1 (cost=0.28..8.30 rows=1 width=49) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((lockid)::text = (lk.lockid)::text)
25. 0.008 0.008 ↑ 1.0 1 1

Index Scan using houseinfo_pk on houseinfo hi_1 (cost=0.28..0.75 rows=1 width=109) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((houseid)::text = (li_1.houseid)::text)
26. 0.164 0.164 ↓ 1.0 302 1

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

  • Filter: ("代码" IS NOT NULL)
27. 0.128 0.128 ↓ 1.3 304 1

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

28. 0.226 191.827 ↑ 47.0 1 1

GroupAggregate (cost=30,416.29..30,417.35 rows=47 width=72) (actual time=191.827..191.827 rows=1 loops=1)

  • Group Key: opendoorrecord_1.lockid, opendoorrecord_1.userid
29. 0.060 191.601 ↓ 1.7 82 1

Sort (cost=30,416.29..30,416.41 rows=47 width=52) (actual time=191.569..191.601 rows=82 loops=1)

  • Sort Key: opendoorrecord_1.lockid
  • Sort Method: quicksort Memory: 36kB
30. 8.730 191.541 ↓ 1.7 82 1

Gather (cost=1,000.00..30,414.98 rows=47 width=52) (actual time=77.559..191.541 rows=82 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
31. 182.811 182.811 ↓ 1.4 27 3

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

  • Filter: (((userid)::text = '13002616410'::text) AND ((opentime)::date >= '2018-04-25'::date))
  • Rows Removed by Filter: 408824
32. 0.014 0.014 ↑ 1.0 1 1

Index Scan using "管理单位代码_pkey" on "管理单位代码" gl (cost=0.27..0.42 rows=1 width=31) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (id = ys."管理单位代码_id")
33. 0.001 6.911 ↓ 0.0 0 1

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

  • Group Key: lockkeyinfo.keyid, lockkeyinfo.lockid, lockkeyinfo.useraccount, lockkeyinfo.authtime, lockkeyinfo.keytype, lockkeyinfo.expireddate, (NULL::text)
34. 0.002 6.910 ↓ 0.0 0 1

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

35. 2.527 2.527 ↓ 0.0 0 1

Seq Scan on lockkeyinfo (cost=0.00..1,058.36 rows=1 width=140) (actual time=2.527..2.527 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
36. 0.002 4.381 ↓ 0.0 0 1

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

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

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

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

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

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

Index Scan using lockinfo_pk on lockinfo li (cost=0.28..8.30 rows=1 width=49) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((lockid)::text = (lockkeyinfo.lockid)::text)
40. 0.007 0.007 ↓ 0.0 0 1

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

  • Index Cond: ((houseid)::text = (li.houseid)::text)