explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6qDV

Settings
# exclusive inclusive rows x rows loops node
1. 58.645 4,925.516 ↓ 19.8 3,968 1

GroupAggregate (cost=10,335.37..10,587.67 rows=200 width=93) (actual time=4,864.933..4,925.516 rows=3,968 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 20.781 4,866.871 ↓ 7.1 23,519 1

Sort (cost=10,335.37..10,343.68 rows=3,324 width=114) (actual time=4,864.901..4,866.871 rows=23,519 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: quicksort Memory: 3580kB
3. 33.306 4,846.090 ↓ 7.1 23,519 1

Nested Loop (cost=4,580.37..10,140.94 rows=3,324 width=114) (actual time=805.294..4,846.090 rows=23,519 loops=1)

4. 97.849 3,346.459 ↓ 42.0 69,825 1

Nested Loop (cost=4,579.55..7,328.00 rows=1,662 width=180) (actual time=804.329..3,346.459 rows=69,825 loops=1)

5. 206.332 872.546 ↓ 68.5 125,056 1

HashAggregate (cost=4,579.13..4,597.38 rows=1,825 width=156) (actual time=804.135..872.546 rows=125,056 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
6. 43.312 666.214 ↓ 109.5 199,764 1

Append (cost=0.84..4,570.00 rows=1,825 width=156) (actual time=0.038..666.214 rows=199,764 loops=1)

7. 19.653 367.177 ↓ 85.2 54,303 1

Nested Loop (cost=0.84..583.03 rows=637 width=31) (actual time=0.038..367.177 rows=54,303 loops=1)

8. 28.048 28.048 ↓ 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.025..28.048 rows=53,246 loops=1)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
9. 319.476 319.476 ↑ 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.004..0.006 rows=1 loops=53,246)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 1
10. 10.778 255.725 ↓ 122.4 145,461 1

Gather (cost=1,076.35..3,959.60 rows=1,188 width=31) (actual time=87.907..255.725 rows=145,461 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 72.025 244.947 ↓ 98.0 48,487 3 / 3

Hash Join (cost=76.35..2,840.80 rows=495 width=31) (actual time=101.245..244.947 rows=48,487 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo_1.houseid)::text)
12. 73.124 73.124 ↑ 1.2 80,191 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,502.14 rows=99,922 width=62) (actual time=0.010..73.124 rows=80,191 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7385
13. 31.788 99.798 ↓ 88.6 53,246 3 / 3

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
14. 68.010 68.010 ↓ 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.057..68.010 rows=53,246 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
15. 2,376.064 2,376.064 ↑ 1.0 1 125,056

Index Scan using billrecord_lockid_idx on billrecord (cost=0.42..1.48 rows=1 width=43) (actual time=0.017..0.019 rows=1 loops=125,056)

  • Index Cond: ((lockid)::text = (lockkeyinfo.lockid)::text)
  • Filter: ((billstate = 0) AND ((billcreatetime)::text < (((timezone('PRC'::text, now()) - '1 mon'::interval))::date)::text))
  • Rows Removed by Filter: 7
16. 69.825 1,466.325 ↓ 0.0 0 69,825

Append (cost=0.82..1.65 rows=2 width=32) (actual time=0.020..0.021 rows=0 loops=69,825)

17. 0.000 837.900 ↓ 0.0 0 69,825

Limit (cost=0.82..0.82 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=69,825)

18. 69.825 837.900 ↓ 0.0 0 69,825

Unique (cost=0.82..0.82 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=69,825)

19. 209.475 768.075 ↓ 0.0 0 69,825

Sort (cost=0.82..0.82 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=69,825)

  • Sort Key: (((opendoorrecord.opentime)::date)::text)
  • Sort Method: quicksort Memory: 25kB
20. 558.600 558.600 ↑ 1.0 1 69,825

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.81 rows=1 width=32) (actual time=0.006..0.008 rows=1 loops=69,825)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 3810
21. 69.825 558.600 ↓ 0.0 0 69,825

Subquery Scan on *SELECT* 2 (cost=0.57..0.81 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=69,825)

22. 139.650 488.775 ↓ 0.0 0 69,825

Limit (cost=0.57..0.80 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=69,825)

23. 349.125 349.125 ↓ 0.0 0 69,825

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.005..0.005 rows=0 loops=69,825)

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