explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BsCR

Settings
# exclusive inclusive rows x rows loops node
1. 15,810.931 39,548.763 ↓ 4,261.0 42,610 1

GroupAggregate (cost=3,675.36..3,675.98 rows=10 width=14) (actual time=21,391.234..39,548.763 rows=42,610 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 7,162.541 23,737.832 ↓ 728,051.8 7,280,518 1

Sort (cost=3,675.36..3,675.38 rows=10 width=32) (actual time=21,391.189..23,737.832 rows=7,280,518 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: external merge Disk: 300680kB
3. 1,294.226 16,575.291 ↓ 728,051.8 7,280,518 1

Nested Loop (cost=3,228.91..3,675.19 rows=10 width=32) (actual time=426.375..16,575.291 rows=7,280,518 loops=1)

4. 38.624 481.462 ↓ 86.5 54,211 1

Unique (cost=3,228.35..3,236.19 rows=627 width=70) (actual time=424.186..481.462 rows=54,211 loops=1)

5. 111.595 442.838 ↓ 86.5 54,211 1

Sort (cost=3,228.35..3,229.92 rows=627 width=70) (actual time=424.183..442.838 rows=54,211 loops=1)

  • Sort Key: lockkeyinfo.houseid, lockkeyinfo.lockid, lockkeyinfo.keyholder, lockkeyinfo.userrole
  • Sort Method: quicksort Memory: 9160kB
6. 56.751 331.243 ↓ 86.5 54,211 1

Gather (cost=1,075.57..3,199.22 rows=627 width=70) (actual time=140.065..331.243 rows=54,211 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 36.874 274.492 ↓ 69.2 18,070 3 / 3

Hash Join (cost=75.57..2,136.52 rows=261 width=70) (actual time=166.852..274.492 rows=18,070 loops=3)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo.houseid)::text)
8. 70.946 70.946 ↑ 1.2 41,916 3 / 3

Parallel Seq Scan on lockkeyinfo (cost=0.00..1,923.76 rows=52,263 width=70) (actual time=0.016..70.946 rows=41,916 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 26988
9. 63.292 166.672 ↓ 89.5 53,243 3 / 3

Hash (cost=68.13..68.13 rows=595 width=35) (actual time=166.672..166.672 rows=53,243 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
10. 103.380 103.380 ↓ 89.5 53,243 3 / 3

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.13 rows=595 width=35) (actual time=0.063..103.380 rows=53,243 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
11. 14,799.603 14,799.603 ↓ 134.0 134 54,211

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.56..0.68 rows=1 width=51) (actual time=0.089..0.273 rows=134 loops=54,211)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text))
  • Heap Fetches: 12048
Planning time : 0.601 ms
Execution time : 39,564.453 ms