explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lgz

Settings
# exclusive inclusive rows x rows loops node
1. 3,569.848 14,226.834 ↓ 18,560.0 37,120 1

GroupAggregate (cost=3,688.46..3,688.55 rows=2 width=13) (actual time=10,452.055..14,226.834 rows=37,120 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 1,540.632 10,656.986 ↓ 642,423.5 1,284,847 1

Sort (cost=3,688.46..3,688.47 rows=2 width=32) (actual time=10,452.010..10,656.986 rows=1,284,847 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: quicksort Memory: 130627kB
3. 528.873 9,116.354 ↓ 642,423.5 1,284,847 1

Nested Loop (cost=3,231.12..3,688.45 rows=2 width=32) (actual time=444.353..9,116.354 rows=1,284,847 loops=1)

4. 45.957 508.850 ↓ 86.5 54,219 1

Unique (cost=3,230.54..3,238.38 rows=627 width=70) (actual time=444.129..508.850 rows=54,219 loops=1)

5. 107.857 462.893 ↓ 86.5 54,219 1

Sort (cost=3,230.54..3,232.11 rows=627 width=70) (actual time=444.127..462.893 rows=54,219 loops=1)

  • Sort Key: lockkeyinfo.houseid, lockkeyinfo.lockid, lockkeyinfo.keyholder, lockkeyinfo.userrole
  • Sort Method: quicksort Memory: 9161kB
6. 66.192 355.036 ↓ 86.5 54,219 1

Gather (cost=1,075.57..3,201.41 rows=627 width=70) (actual time=143.099..355.036 rows=54,219 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 50.424 288.844 ↓ 69.2 18,073 3 / 3

Hash Join (cost=75.57..2,138.71 rows=261 width=70) (actual time=168.067..288.844 rows=18,073 loops=3)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo.houseid)::text)
8. 70.532 70.532 ↑ 1.2 41,931 3 / 3

Parallel Seq Scan on lockkeyinfo (cost=0.00..1,925.81 rows=52,318 width=70) (actual time=0.015..70.532 rows=41,931 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 26991
9. 36.068 167.888 ↓ 89.5 53,243 3 / 3

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
11. 8,078.631 8,078.631 ↓ 24.0 24 54,219

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.70 rows=1 width=51) (actual time=0.103..0.149 rows=24 loops=54,219)

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