explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8tZ1

Settings
# exclusive inclusive rows x rows loops node
1. 473.419 49,949.199 ↓ 169.9 40,602 1

GroupAggregate (cost=416,876.47..416,880.66 rows=239 width=20) (actual time=48,991.945..49,949.199 rows=40,602 loops=1)

  • Group Key: opendoorrecord.userid
2. 2,465.249 49,475.780 ↓ 13,180.5 3,150,128 1

Sort (cost=416,876.47..416,877.07 rows=239 width=12) (actual time=48,991.931..49,475.780 rows=3,150,128 loops=1)

  • Sort Key: opendoorrecord.userid
  • Sort Method: external merge Disk: 68896kB
3. 3,567.706 47,010.531 ↓ 13,180.5 3,150,128 1

Merge Join (cost=1,001.44..416,867.03 rows=239 width=12) (actual time=98.029..47,010.531 rows=3,150,128 loops=1)

  • Merge Cond: ((lockkeyinfo.lockid)::text = (opendoorrecord.lockid)::text)
  • Join Filter: (((opendoorrecord.userid)::text = (lockkeyinfo.keyholder)::text) OR ((opendoorrecord.userid)::text = (lockkeyinfo.useraccount)::text))
  • Rows Removed by Join Filter: 4113736
4. 0.000 152.698 ↑ 1.1 53,911 1

Gather Merge (cost=1,000.86..34,119.26 rows=57,386 width=43) (actual time=7.158..152.698 rows=53,911 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 44.467 434.646 ↑ 1.3 17,970 3 / 3

Nested Loop (cost=0.84..26,495.47 rows=23,911 width=43) (actual time=0.534..434.646 rows=17,970 loops=3)

6. 141.293 141.293 ↑ 1.3 41,481 3 / 3

Parallel Index Scan using idx_index_lock_lockkeyinf_1589794062_0 on lockkeyinfo (cost=0.42..3,069.76 rows=52,798 width=78) (actual time=0.337..141.293 rows=41,481 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 26773
7. 248.886 248.886 ↓ 0.0 0 124,443 / 3

Index Scan using idx_index_hous_houseinfo_1588853107_83 on houseinfo (cost=0.42..0.44 rows=1 width=35) (actual time=0.006..0.006 rows=0 loops=124,443)

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 1
8. 43,290.127 43,290.127 ↓ 1.4 12,455,911 1

Index Scan using idx_openrecord_opendoorre_1588854611_141 on opendoorrecord (cost=0.57..253,437.79 rows=8,619,625 width=31) (actual time=1.145..43,290.127 rows=12,455,911 loops=1)

  • Index Cond: ((opentime)::text >= (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text)
Planning time : 1.991 ms
Execution time : 49,957.204 ms