explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3ox2

Settings
# exclusive inclusive rows x rows loops node
1. 7,687.293 21,911.941 ↓ 1,882.8 54,600 1

GroupAggregate (cost=5,593.56..5,594.79 rows=29 width=59) (actual time=12,251.514..21,911.941 rows=54,600 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 6,159.391 14,224.648 ↓ 297,509.0 8,627,761 1

Sort (cost=5,593.56..5,593.63 rows=29 width=78) (actual time=12,251.464..14,224.648 rows=8,627,761 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: external merge Disk: 356168kB
3. 1,270.881 8,065.257 ↓ 297,509.0 8,627,761 1

Nested Loop (cost=4,307.31..5,592.86 rows=29 width=78) (actual time=749.120..8,065.257 rows=8,627,761 loops=1)

4. 199.219 802.296 ↓ 68.9 124,835 1

HashAggregate (cost=4,306.75..4,324.86 rows=1,811 width=156) (actual time=748.392..802.296 rows=124,835 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
5. 28.196 603.077 ↓ 110.1 199,325 1

Append (cost=0.42..4,297.69 rows=1,811 width=156) (actual time=0.043..603.077 rows=199,325 loops=1)

6. 40.096 226.168 ↓ 86.2 54,234 1

Nested Loop (cost=0.42..323.03 rows=629 width=31) (actual time=0.042..226.168 rows=54,234 loops=1)

7. 26.340 26.340 ↓ 89.2 53,244 1

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.37 rows=597 width=35) (actual time=0.030..26.340 rows=53,244 loops=1)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
8. 159.732 159.732 ↑ 2.0 1 53,244

Index Scan using lockkeyinfo_houseid_idx1 on lockkeyinfo (cost=0.00..0.41 rows=2 width=66) (actual time=0.002..0.003 rows=1 loops=53,244)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Rows Removed by Index Recheck: 0
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
9. 0.000 348.713 ↓ 122.8 145,091 1

Gather (cost=1,075.83..3,947.50 rows=1,182 width=31) (actual time=48.844..348.713 rows=145,091 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 63.125 395.493 ↓ 98.3 48,364 3 / 3

Hash Join (cost=75.83..2,829.30 rows=492 width=31) (actual time=83.182..395.493 rows=48,364 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
11. 249.941 249.941 ↑ 1.2 79,872 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,492.21 rows=99,525 width=62) (actual time=0.617..249.941 rows=79,872 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7365
12. 26.352 82.427 ↓ 89.2 53,244 3 / 3

Hash (cost=68.37..68.37 rows=597 width=35) (actual time=82.426..82.427 rows=53,244 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
13. 56.075 56.075 ↓ 89.2 53,244 3 / 3

Index Scan using houseinfo_left_idx on houseinfo houseinfo_1 (cost=0.42..68.37 rows=597 width=35) (actual time=0.063..56.075 rows=53,244 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
14. 5,992.080 5,992.080 ↓ 69.0 69 124,835

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.56..0.68 rows=1 width=51) (actual time=0.014..0.048 rows=69 loops=124,835)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text))
  • Heap Fetches: 35426
Planning time : 0.804 ms
Execution time : 21,917.002 ms