explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TOLg

Settings
# exclusive inclusive rows x rows loops node
1. 81.487 4,514.408 ↓ 250.2 50,035 1

HashAggregate (cost=6,914.88..6,917.38 rows=200 width=90) (actual time=4,497.094..4,514.408 rows=50,035 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 0.000 4,432.921 ↓ 31.1 56,687 1

Nested Loop (cost=4,579.70..6,905.75 rows=1,825 width=90) (actual time=636.776..4,432.921 rows=56,687 loops=1)

3. 202.181 682.527 ↓ 68.5 125,055 1

HashAggregate (cost=4,578.88..4,597.13 rows=1,825 width=156) (actual time=636.480..682.527 rows=125,055 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
4. 35.038 480.346 ↓ 109.5 199,759 1

Append (cost=0.84..4,569.75 rows=1,825 width=156) (actual time=0.034..480.346 rows=199,759 loops=1)

5. 20.037 252.500 ↓ 85.2 54,302 1

Nested Loop (cost=0.84..583.03 rows=637 width=31) (actual time=0.033..252.500 rows=54,302 loops=1)

6. 19.479 19.479 ↓ 88.6 53,246 1

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.84 rows=601 width=35) (actual time=0.021..19.479 rows=53,246 loops=1)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
7. 212.984 212.984 ↑ 2.0 1 53,246

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.84 rows=2 width=66) (actual time=0.003..0.004 rows=1 loops=53,246)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
8. 0.000 192.808 ↓ 122.4 145,457 1

Gather (cost=1,076.35..3,959.34 rows=1,188 width=31) (actual time=48.204..192.808 rows=145,457 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 72.980 238.696 ↓ 98.0 48,486 3 / 3

Hash Join (cost=76.35..2,840.54 rows=495 width=31) (actual time=95.371..238.696 rows=48,486 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
10. 70.487 70.487 ↑ 1.2 80,185 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,501.92 rows=99,912 width=62) (actual time=0.009..70.487 rows=80,185 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7385
11. 29.613 95.229 ↓ 88.6 53,246 3 / 3

Hash (cost=68.84..68.84 rows=601 width=35) (actual time=95.229..95.229 rows=53,246 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
12. 65.616 65.616 ↓ 88.6 53,246 3 / 3

Index Scan using houseinfo_left_idx on houseinfo houseinfo_1 (cost=0.42..68.84 rows=601 width=35) (actual time=0.060..65.616 rows=53,246 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
13. 125.055 3,751.650 ↓ 0.0 0 125,055

Limit (cost=0.82..1.24 rows=1 width=32) (actual time=0.029..0.030 rows=0 loops=125,055)

14. 86.155 3,626.595 ↓ 0.0 0 125,055

Append (cost=0.82..1.65 rows=2 width=32) (actual time=0.029..0.029 rows=0 loops=125,055)

15. 0.000 3,126.375 ↓ 0.0 0 125,055

Limit (cost=0.82..0.82 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=125,055)

16. 0.000 3,126.375 ↓ 0.0 0 125,055

Unique (cost=0.82..0.82 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=125,055)

17. 750.330 3,126.375 ↓ 0.0 0 125,055

Sort (cost=0.82..0.82 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=125,055)

  • Sort Key: (((opendoorrecord.opentime)::date)::text)
  • Sort Method: quicksort Memory: 25kB
18. 2,376.045 2,376.045 ↓ 11.0 11 125,055

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.81 rows=1 width=32) (actual time=0.008..0.019 rows=11 loops=125,055)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 60340
19. 82.813 414.065 ↓ 0.0 0 82,813

Subquery Scan on *SELECT* 2 (cost=0.57..0.81 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=82,813)

20. 331.252 331.252 ↓ 0.0 0 82,813

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.57..0.80 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=82,813)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime < (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text))
  • Heap Fetches: 37
Planning time : 0.733 ms
Execution time : 4,518.397 ms