explain.depesz.com

PostgreSQL's explain analyze made readable

Result: beQg

Settings
# exclusive inclusive rows x rows loops node
1. 22.307 4,033.738 ↓ 197.5 39,491 1

Group (cost=7,503.03..7,505.29 rows=200 width=58) (actual time=4,006.992..4,033.738 rows=39,491 loops=1)

  • Group Key: keyhistoryinfo.keyholder
2. 78.862 4,011.431 ↓ 93.3 42,181 1

Sort (cost=7,503.03..7,504.16 rows=452 width=58) (actual time=4,006.989..4,011.431 rows=42,181 loops=1)

  • Sort Key: keyhistoryinfo.keyholder
  • Sort Method: quicksort Memory: 3551kB
3. 397.333 3,932.569 ↓ 93.3 42,181 1

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

4. 421.668 1,289.412 ↓ 69.0 124,768 1

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

  • Group Key: keyhistoryinfo.lockid, keyhistoryinfo.keyholder
5. 248.089 867.744 ↓ 110.2 199,199 1

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

  • Workers Planned: 2
  • Workers Launched: 2
6. 24.729 619.655 ↓ 36.7 66,400 3 / 3

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

7. 126.774 376.629 ↓ 98.2 48,327 3 / 3

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

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo.houseid)::text)
8. 91.839 91.839 ↑ 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..91.839 rows=79,779 loops=3)

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

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

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

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

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

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo_1.houseid)::text)
12. 64.302 64.302 ↓ 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.015..96.453 rows=62,898 loops=2)

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

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
14. 68.973 68.973 ↓ 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.069..103.460 rows=53,243 loops=2)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
15. 2,245.824 2,245.824 ↓ 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.018..0.018 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: 5654
Planning time : 0.938 ms
Execution time : 4,039.025 ms