explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ttep

Settings
# exclusive inclusive rows x rows loops node
1. 12.493 2,371.148 ↓ 197.5 39,491 1

Group (cost=7,503.03..7,505.29 rows=200 width=58) (actual time=2,354.393..2,371.148 rows=39,491 loops=1)

  • Group Key: keyhistoryinfo.keyholder
2. 54.140 2,358.655 ↓ 93.3 42,181 1

Sort (cost=7,503.03..7,504.16 rows=452 width=58) (actual time=2,354.392..2,358.655 rows=42,181 loops=1)

  • Sort Key: keyhistoryinfo.keyholder
  • Sort Method: quicksort Memory: 3551kB
3. 227.333 2,304.515 ↓ 93.3 42,181 1

Nested Loop Semi Join (cost=6,182.11..7,483.10 rows=452 width=58) (actual time=901.677..2,304.515 rows=42,181 loops=1)

4. 242.529 954.270 ↓ 69.0 124,768 1

HashAggregate (cost=6,181.54..6,199.62 rows=1,808 width=156) (actual time=901.608..954.270 rows=124,768 loops=1)

  • Group Key: keyhistoryinfo.lockid, keyhistoryinfo.keyholder
5. 221.632 711.741 ↓ 110.2 199,199 1

Gather (cost=1,075.57..6,172.50 rows=1,808 width=156) (actual time=137.714..711.741 rows=199,199 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 15.899 490.109 ↓ 36.7 66,400 3 / 3

Parallel Append (cost=75.57..4,991.70 rows=1,808 width=156) (actual time=152.686..490.109 rows=66,400 loops=3)

7. 79.779 268.049 ↓ 98.2 48,327 3 / 3

Hash Join (cost=75.57..2,825.87 rows=492 width=31) (actual time=103.759..268.049 rows=48,327 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo.houseid)::text)
8. 84.612 84.612 ↑ 1.2 79,779 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,489.34 rows=99,410 width=62) (actual time=0.011..84.612 rows=79,779 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7359
9. 29.336 103.658 ↓ 89.5 53,243 3 / 3

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
11. 42.714 206.161 ↓ 103.9 27,110 2 / 3

Hash Join (cost=75.57..2,138.71 rows=261 width=31) (actual time=159.204..309.242 rows=27,110 loops=2)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo_1.houseid)::text)
12. 57.393 57.393 ↓ 1.2 62,898 2 / 3

Parallel Seq Scan on lockkeyinfo (cost=0.00..1,925.81 rows=52,318 width=66) (actual time=0.012..86.089 rows=62,898 loops=2)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 40486
13. 34.454 106.055 ↓ 89.5 53,243 2 / 3

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

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
15. 1,122.912 1,122.912 ↓ 0.0 0 124,768

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.70 rows=1 width=31) (actual time=0.009..0.009 rows=0 loops=124,768)

  • Index Cond: ((lockid = (keyhistoryinfo.lockid)::text) AND (userid = (keyhistoryinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 5690
Planning time : 0.799 ms
Execution time : 2,373.886 ms