explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RJcQ

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

Group (cost=5,878.44..5,880.70 rows=200 width=58) (actual time=2,440.211..2,456.845 rows=39,491 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 34.469 2,444.205 ↓ 93.3 42,181 1

Sort (cost=5,878.44..5,879.57 rows=452 width=58) (actual time=2,440.209..2,444.205 rows=42,181 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: quicksort Memory: 3551kB
3. 200.503 2,409.736 ↓ 93.3 42,181 1

Nested Loop Semi Join (cost=4,557.52..5,858.51 rows=452 width=58) (actual time=1,037.135..2,409.736 rows=42,181 loops=1)

4. 218.788 1,086.321 ↓ 69.0 124,768 1

HashAggregate (cost=4,556.95..4,575.03 rows=1,808 width=156) (actual time=1,037.064..1,086.321 rows=124,768 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
5. 69.174 867.533 ↓ 110.2 199,200 1

Append (cost=0.84..4,547.91 rows=1,808 width=156) (actual time=0.045..867.533 rows=199,200 loops=1)

6. 68.421 369.564 ↓ 86.5 54,220 1

Nested Loop (cost=0.84..576.82 rows=627 width=31) (actual time=0.044..369.564 rows=54,220 loops=1)

7. 34.928 34.928 ↓ 89.5 53,243 1

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.13 rows=595 width=35) (actual time=0.029..34.928 rows=53,243 loops=1)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
8. 266.215 266.215 ↑ 2.0 1 53,243

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.83 rows=2 width=66) (actual time=0.004..0.005 rows=1 loops=53,243)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
9. 60.173 428.795 ↓ 122.8 144,980 1

Gather (cost=1,075.57..3,943.97 rows=1,181 width=31) (actual time=146.405..428.795 rows=144,980 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 111.853 368.622 ↓ 98.2 48,327 3 / 3

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

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
11. 89.941 89.941 ↑ 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.012..89.941 rows=79,779 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7359
12. 52.475 166.828 ↓ 89.5 53,243 3 / 3

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
14. 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 = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 5788
Planning time : 1.012 ms
Execution time : 2,459.571 ms