explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KuFa

Settings
# exclusive inclusive rows x rows loops node
1. 63.426 5,256.891 ↓ 103.2 20,632 1

HashAggregate (cost=7,609.73..7,612.23 rows=200 width=90) (actual time=5,247.910..5,256.891 rows=20,632 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 157.050 5,193.465 ↓ 54.7 99,016 1

Nested Loop (cost=4,564.11..7,600.67 rows=1,811 width=78) (actual time=728.474..5,193.465 rows=99,016 loops=1)

3. 29.411 3,549.463 ↓ 102.6 185,869 1

Nested Loop (cost=4,563.53..6,120.18 rows=1,811 width=156) (actual time=728.434..3,549.463 rows=185,869 loops=1)

4. 160.442 773.638 ↓ 68.9 124,837 1

HashAggregate (cost=4,562.72..4,580.83 rows=1,811 width=156) (actual time=728.269..773.638 rows=124,837 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
5. 28.582 613.196 ↓ 110.1 199,331 1

Append (cost=0.84..4,553.66 rows=1,811 width=156) (actual time=1.025..613.196 rows=199,331 loops=1)

6. 39.928 381.862 ↓ 86.2 54,235 1

Nested Loop (cost=0.84..578.76 rows=629 width=31) (actual time=1.025..381.862 rows=54,235 loops=1)

7. 22.470 22.470 ↓ 89.2 53,244 1

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.37 rows=597 width=35) (actual time=0.026..22.470 rows=53,244 loops=1)

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

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

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
9. 0.000 202.752 ↓ 122.8 145,096 1

Gather (cost=1,075.83..3,947.74 rows=1,182 width=31) (actual time=79.541..202.752 rows=145,096 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 75.743 220.353 ↓ 98.3 48,365 3 / 3

Hash Join (cost=75.83..2,829.54 rows=492 width=31) (actual time=86.548..220.353 rows=48,365 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
11. 58.224 58.224 ↑ 1.2 79,876 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,492.43 rows=99,534 width=62) (actual time=0.010..58.224 rows=79,876 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7366
12. 23.518 86.386 ↓ 89.2 53,244 3 / 3

Hash (cost=68.37..68.37 rows=597 width=35) (actual time=86.386..86.386 rows=53,244 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
13. 62.868 62.868 ↓ 89.2 53,244 3 / 3

Index Scan using houseinfo_left_idx on houseinfo houseinfo_1 (cost=0.42..68.37 rows=597 width=35) (actual time=0.067..62.868 rows=53,244 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
14. 124.837 2,746.414 ↑ 1.0 1 124,837

Limit (cost=0.81..0.82 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=124,837)

15. 124.837 2,621.577 ↑ 1.0 1 124,837

Unique (cost=0.81..0.82 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=124,837)

16. 374.511 2,496.740 ↓ 3.0 3 124,837

Sort (cost=0.81..0.82 rows=1 width=4) (actual time=0.020..0.020 rows=3 loops=124,837)

  • Sort Key: ((opendoorrecord.opentime)::date)
  • Sort Method: quicksort Memory: 25kB
17. 2,122.229 2,122.229 ↓ 10.0 10 124,837

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.80 rows=1 width=4) (actual time=0.008..0.017 rows=10 loops=124,837)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 27590
18. 185.869 1,486.952 ↑ 1.0 1 185,869

Limit (cost=0.57..0.80 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=185,869)

19. 1,301.083 1,301.083 ↑ 1.0 1 185,869

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.007..0.007 rows=1 loops=185,869)

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