explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7b9S

Settings
# exclusive inclusive rows x rows loops node
1. 14,939.998 49,324.452 ↓ 4,261.0 42,610 1

GroupAggregate (cost=3,681.39..3,682.01 rows=10 width=60) (actual time=32,195.170..49,324.452 rows=42,610 loops=1)

  • Group Key: keys.keyholder
2.          

CTE keys

3. 57.352 504.658 ↓ 86.5 54,212 1

Unique (cost=3,228.11..3,235.95 rows=627 width=70) (actual time=424.450..504.658 rows=54,212 loops=1)

4. 145.753 447.306 ↓ 86.5 54,212 1

Sort (cost=3,228.11..3,229.68 rows=627 width=70) (actual time=424.448..447.306 rows=54,212 loops=1)

  • Sort Key: lockkeyinfo.houseid, lockkeyinfo.lockid, lockkeyinfo.keyholder, lockkeyinfo.userrole
  • Sort Method: quicksort Memory: 9160kB
5. 51.100 301.553 ↓ 86.5 54,212 1

Gather (cost=1,075.57..3,198.98 rows=627 width=70) (actual time=106.471..301.553 rows=54,212 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 39.516 250.453 ↓ 69.2 18,071 3 / 3

Hash Join (cost=75.57..2,136.28 rows=261 width=70) (actual time=141.025..250.453 rows=18,071 loops=3)

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

Parallel Seq Scan on lockkeyinfo (cost=0.00..1,923.54 rows=52,257 width=70) (actual time=0.015..70.097 rows=41,916 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 26988
8. 46.673 140.840 ↓ 89.5 53,243 3 / 3

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
9. 94.167 94.167 ↓ 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.725..94.167 rows=53,243 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
10. 8,226.838 34,384.454 ↓ 727,949.4 7,279,494 1

Sort (cost=445.44..445.46 rows=10 width=78) (actual time=32,195.121..34,384.454 rows=7,279,494 loops=1)

  • Sort Key: keys.keyholder
  • Sort Method: external merge Disk: 300640kB
11. 1,495.580 26,157.616 ↓ 727,949.4 7,279,494 1

Nested Loop (cost=0.56..445.27 rows=10 width=78) (actual time=425.486..26,157.616 rows=7,279,494 loops=1)

12. 537.696 537.696 ↓ 86.5 54,212 1

CTE Scan on keys (cost=0.00..12.54 rows=627 width=156) (actual time=424.453..537.696 rows=54,212 loops=1)

13. 24,124.340 24,124.340 ↓ 134.0 134 54,212

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

  • Index Cond: ((lockid = (keys.lockid)::text) AND (userid = (keys.keyholder)::text))
  • Heap Fetches: 10982
Planning time : 0.551 ms
Execution time : 49,338.925 ms