explain.depesz.com

PostgreSQL's explain analyze made readable

Result: InEc

Settings
# exclusive inclusive rows x rows loops node
1. 65.058 4,173.591 ↓ 196.7 39,343 1

HashAggregate (cost=5,868.73..5,870.73 rows=200 width=58) (actual time=4,166.807..4,173.591 rows=39,343 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 128.363 4,108.533 ↓ 102.6 185,853 1

Nested Loop (cost=4,307.56..5,864.21 rows=1,811 width=58) (actual time=565.786..4,108.533 rows=185,853 loops=1)

3. 162.304 609.625 ↓ 68.9 124,835 1

HashAggregate (cost=4,306.75..4,324.86 rows=1,811 width=156) (actual time=565.628..609.625 rows=124,835 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
4. 28.714 447.321 ↓ 110.1 199,325 1

Append (cost=0.42..4,297.69 rows=1,811 width=156) (actual time=0.037..447.321 rows=199,325 loops=1)

5. 42.223 224.054 ↓ 86.2 54,233 1

Nested Loop (cost=0.42..323.03 rows=629 width=31) (actual time=0.037..224.054 rows=54,233 loops=1)

6. 22.099 22.099 ↓ 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.099 rows=53,244 loops=1)

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

Index Scan using lockkeyinfo_houseid_idx1 on lockkeyinfo (cost=0.00..0.41 rows=2 width=66) (actual time=0.002..0.003 rows=1 loops=53,244)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Rows Removed by Index Recheck: 0
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
8. 0.000 194.553 ↓ 122.8 145,092 1

Gather (cost=1,075.83..3,947.50 rows=1,182 width=31) (actual time=46.873..194.553 rows=145,092 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 66.748 225.759 ↓ 98.3 48,364 3 / 3

Hash Join (cost=75.83..2,829.30 rows=492 width=31) (actual time=94.923..225.759 rows=48,364 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
10. 64.280 64.280 ↑ 1.2 79,872 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,492.21 rows=99,525 width=62) (actual time=0.011..64.280 rows=79,872 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7365
11. 28.838 94.731 ↓ 89.2 53,244 3 / 3

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
12. 65.893 65.893 ↓ 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.081..65.893 rows=53,244 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
13. 0.000 3,370.545 ↑ 1.0 1 124,835

Limit (cost=0.81..0.82 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=124,835)

14. 124.835 3,370.545 ↑ 1.0 1 124,835

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

15. 374.505 3,245.710 ↓ 3.0 3 124,835

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

  • Sort Key: ((opendoorrecord.opentime)::date)
  • Sort Method: quicksort Memory: 25kB
16. 2,871.205 2,871.205 ↓ 10.0 10 124,835

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

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 27311
Planning time : 0.684 ms
Execution time : 4,175.704 ms