explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QB2t

Settings
# exclusive inclusive rows x rows loops node
1. 3,593.887 23,943.725 ↓ 203.0 40,602 1

GroupAggregate (cost=754,620.28..754,630.55 rows=200 width=67) (actual time=19,632.585..23,943.725 rows=40,602 loops=1)

  • Group Key: opendoorrecord.userid
2.          

CTE opendoorrecord

3. 5,726.242 5,726.242 ↓ 1.0 8,694,761 1

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.57..176,218.78 rows=8,621,580 width=51) (actual time=0.038..5,726.242 rows=8,694,761 loops=1)

  • Index Cond: ((opentime)::text >= (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
4. 3,682.143 20,349.838 ↓ 13,183.2 3,150,786 1

Sort (cost=578,401.50..578,402.10 rows=239 width=116) (actual time=19,632.541..20,349.838 rows=3,150,786 loops=1)

  • Sort Key: opendoorrecord.userid
  • Sort Method: external merge Disk: 130576kB
5. 5,673.809 16,667.695 ↓ 13,183.2 3,150,786 1

Hash Join (cost=7,328.96..578,392.06 rows=239 width=116) (actual time=174.429..16,667.695 rows=3,150,786 loops=1)

  • Hash Cond: ((opendoorrecord.lockid)::text = (lockkeyinfo.lockid)::text)
  • Join Filter: (((opendoorrecord.userid)::text = (lockkeyinfo.keyholder)::text) OR ((opendoorrecord.userid)::text = (lockkeyinfo.useraccount)::text))
  • Rows Removed by Join Filter: 4114790
6. 10,819.552 10,819.552 ↓ 1.0 8,694,761 1

CTE Scan on opendoorrecord (cost=0.00..172,431.60 rows=8,621,580 width=214) (actual time=0.040..10,819.552 rows=8,694,761 loops=1)

7. 13.893 174.334 ↑ 1.1 53,912 1

Hash (cost=6,611.37..6,611.37 rows=57,407 width=43) (actual time=174.334..174.334 rows=53,912 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4542kB
8. 40.409 160.441 ↑ 1.1 53,912 1

Hash Join (cost=2,853.73..6,611.37 rows=57,407 width=43) (actual time=60.555..160.441 rows=53,912 loops=1)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo.houseid)::text)
9. 59.530 59.530 ↑ 1.0 124,455 1

Seq Scan on lockkeyinfo (cost=0.00..3,424.89 rows=126,762 width=78) (actual time=0.007..59.530 rows=124,455 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 80324
10. 10.190 60.502 ↑ 1.0 53,181 1

Hash (cost=2,182.05..2,182.05 rows=53,734 width=35) (actual time=60.502..60.502 rows=53,181 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3888kB
11. 50.312 50.312 ↑ 1.0 53,181 1

Seq Scan on houseinfo (cost=0.00..2,182.05 rows=53,734 width=35) (actual time=0.004..50.312 rows=53,181 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 64812
Planning time : 0.528 ms
Execution time : 23,960.335 ms