explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ji2J

Settings
# exclusive inclusive rows x rows loops node
1. 17,414.553 72,035.161 ↓ 214.8 42,954 1

GroupAggregate (cost=90,842.07..90,911.82 rows=200 width=60) (actual time=52,013.685..72,035.161 rows=42,954 loops=1)

  • Group Key: lockkeyinfo.keyholder
2.          

CTE keyholders

3. 185.084 655.751 ↓ 56.2 58,038 1

HashAggregate (cost=3,115.46..3,125.79 rows=1,033 width=12) (actual time=607.437..655.751 rows=58,038 loops=1)

  • Group Key: lockkeyinfo_1.keyholder
4. 91.235 470.667 ↓ 88.0 90,877 1

Gather (cost=1,075.57..3,112.87 rows=1,033 width=12) (actual time=246.833..470.667 rows=90,877 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 116.306 379.432 ↓ 70.4 30,292 3 / 3

Hash Join (cost=75.57..2,009.57 rows=430 width=12) (actual time=194.078..379.432 rows=30,292 loops=3)

  • Hash Cond: ((lockkeyinfo_1.houseid)::text = (houseinfo_1.houseid)::text)
6. 69.234 69.234 ↑ 1.3 68,902 3 / 3

Parallel Seq Scan on lockkeyinfo lockkeyinfo_1 (cost=0.00..1,707.88 rows=86,138 width=47) (actual time=0.017..69.234 rows=68,902 loops=3)

7. 62.858 193.892 ↓ 89.5 53,243 3 / 3

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

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
9. 9,426.079 54,620.608 ↓ 6,010.8 8,084,517 1

Sort (cost=87,716.28..87,719.64 rows=1,345 width=78) (actual time=52,013.441..54,620.608 rows=8,084,517 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: external merge Disk: 333760kB
10. 1,696.562 45,194.529 ↓ 6,010.8 8,084,517 1

Nested Loop (cost=19,818.10..87,646.38 rows=1,345 width=78) (actual time=1,511.054..45,194.529 rows=8,084,517 loops=1)

11. 97.574 1,988.653 ↑ 1.2 69,881 1

Hash Join (cost=19,817.54..29,399.11 rows=82,391 width=214) (actual time=1,510.975..1,988.653 rows=69,881 loops=1)

  • Hash Cond: ((lockkeyinfo.keyholder)::text = (keyholders.keyholder)::text)
12. 188.875 1,084.424 ↑ 1.3 125,307 1

Hash Join (cost=19,789.80..28,022.21 rows=164,782 width=156) (actual time=704.305..1,084.424 rows=125,307 loops=1)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo.houseid)::text)
13. 458.508 756.550 ↑ 1.5 247,850 1

HashAggregate (cost=16,920.35..20,558.97 rows=363,862 width=258) (actual time=565.107..756.550 rows=247,850 loops=1)

  • Group Key: lockkeyinfo.houseid, lockkeyinfo.lockid, lockkeyinfo.keyholder, lockkeyinfo.userrole
14. 43.344 298.042 ↓ 1.0 364,940 1

Append (cost=0.00..13,281.73 rows=363,862 width=258) (actual time=0.012..298.042 rows=364,940 loops=1)

15. 105.567 105.567 ↓ 1.0 125,748 1

Seq Scan on lockkeyinfo (cost=0.00..3,430.65 rows=125,404 width=70) (actual time=0.011..105.567 rows=125,748 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 80959
16. 149.131 149.131 ↓ 1.0 239,192 1

Seq Scan on keyhistoryinfo (cost=0.00..4,393.15 rows=238,458 width=66) (actual time=0.015..149.131 rows=239,192 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 22075
17. 25.246 138.999 ↑ 1.0 53,243 1

Hash (cost=2,195.59..2,195.59 rows=53,909 width=35) (actual time=138.999..138.999 rows=53,243 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3892kB
18. 113.753 113.753 ↑ 1.0 53,243 1

Seq Scan on houseinfo (cost=0.00..2,195.59 rows=53,909 width=35) (actual time=0.017..113.753 rows=53,243 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 65798
19. 30.391 806.655 ↓ 290.2 58,037 1

Hash (cost=25.24..25.24 rows=200 width=58) (actual time=806.655..806.655 rows=58,037 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3021kB
20. 73.236 776.264 ↓ 290.2 58,038 1

HashAggregate (cost=23.24..25.24 rows=200 width=58) (actual time=765.043..776.264 rows=58,038 loops=1)

  • Group Key: (keyholders.keyholder)::text
21. 703.028 703.028 ↓ 56.2 58,038 1

CTE Scan on keyholders (cost=0.00..20.66 rows=1,033 width=58) (actual time=607.441..703.028 rows=58,038 loops=1)

22. 41,509.314 41,509.314 ↓ 116.0 116 69,881

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.56..0.70 rows=1 width=51) (actual time=0.108..0.594 rows=116 loops=69,881)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text))
  • Heap Fetches: 8084680
Planning time : 40.286 ms
Execution time : 72,072.442 ms