explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nj45

Settings
# exclusive inclusive rows x rows loops node
1. 4,602.465 31,716.411 ↓ 507.5 40,603 1

GroupAggregate (cost=639,699.09..639,702.69 rows=80 width=67) (actual time=26,216.131..31,716.411 rows=40,603 loops=1)

  • Group Key: opendoorrecord.userid
2.          

CTE opendoorrecord

3. 5,640.672 5,640.672 ↓ 1.0 8,694,347 1

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.57..176,207.65 rows=8,621,084 width=51) (actual time=0.030..5,640.672 rows=8,694,347 loops=1)

  • Index Cond: ((opentime)::text >= (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
4. 3,965.179 27,113.946 ↓ 39,384.1 3,150,724 1

Sort (cost=463,491.43..463,491.63 rows=80 width=116) (actual time=26,216.089..27,113.946 rows=3,150,724 loops=1)

  • Sort Key: opendoorrecord.userid
  • Sort Method: external merge Disk: 130576kB
5. 5,400.337 23,148.767 ↓ 39,384.1 3,150,724 1

Hash Join (cost=7,328.41..463,488.91 rows=80 width=116) (actual time=190.022..23,148.767 rows=3,150,724 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: 4114657
6. 17,558.497 17,558.497 ↓ 3.0 8,694,347 1

CTE Scan on opendoorrecord (cost=0.00..323,290.65 rows=2,873,695 width=214) (actual time=0.034..17,558.497 rows=8,694,347 loops=1)

  • Filter: ((opentime)::text >= (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
7. 16.867 189.933 ↑ 1.1 53,912 1

Hash (cost=6,610.91..6,610.91 rows=57,400 width=43) (actual time=189.933..189.933 rows=53,912 loops=1)

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

Hash Join (cost=2,853.73..6,610.91 rows=57,400 width=43) (actual time=64.871..173.066 rows=53,912 loops=1)

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

Seq Scan on lockkeyinfo (cost=0.00..3,424.46 rows=126,747 width=78) (actual time=0.006..65.117 rows=124,454 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 80323
10. 11.840 64.792 ↑ 1.0 53,181 1

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

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

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

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 64812
Planning time : 0.551 ms
Execution time : 31,729.170 ms