explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5wCb

Settings
# exclusive inclusive rows x rows loops node
1. 4,116.458 15,190.790 ↓ 249.2 49,835 1

GroupAggregate (cost=14,876.30..16,986.35 rows=200 width=93) (actual time=10,663.100..15,190.790 rows=49,835 loops=1)

  • Group Key: lockkeyinfo.keyholder
2. 1,798.560 11,074.332 ↓ 63.6 1,787,978 1

Sort (cost=14,876.30..14,946.53 rows=28,094 width=114) (actual time=10,662.156..11,074.332 rows=1,787,978 loops=1)

  • Sort Key: lockkeyinfo.keyholder
  • Sort Method: external merge Disk: 104312kB
3. 819.071 9,275.772 ↓ 63.6 1,787,978 1

Nested Loop (cost=4,580.12..12,800.44 rows=28,094 width=114) (actual time=628.217..9,275.772 rows=1,787,978 loops=1)

4. 162.340 6,230.811 ↓ 61.0 222,589 1

Nested Loop (cost=4,579.70..7,704.19 rows=3,650 width=188) (actual time=628.179..6,230.811 rows=222,589 loops=1)

5. 184.861 691.063 ↓ 68.5 125,056 1

HashAggregate (cost=4,578.88..4,597.13 rows=1,825 width=156) (actual time=627.819..691.063 rows=125,056 loops=1)

  • Group Key: lockkeyinfo.lockid, lockkeyinfo.keyholder
6. 28.039 506.202 ↓ 109.5 199,764 1

Append (cost=0.84..4,569.75 rows=1,825 width=156) (actual time=0.079..506.202 rows=199,764 loops=1)

7. 30.871 271.438 ↓ 85.2 54,303 1

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

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

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
9. 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
10. 0.000 206.725 ↓ 122.4 145,461 1

Gather (cost=1,076.35..3,959.34 rows=1,188 width=31) (actual time=49.312..206.725 rows=145,461 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 73.365 245.060 ↓ 98.0 48,487 3 / 3

Hash Join (cost=76.35..2,840.54 rows=495 width=31) (actual time=95.707..245.060 rows=48,487 loops=3)

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

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

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

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

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

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

Append (cost=0.82..1.65 rows=2 width=32) (actual time=0.037..0.043 rows=2 loops=125,056)

16. 0.000 4,126.848 ↑ 1.0 1 125,056

Limit (cost=0.82..0.82 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=125,056)

17. 250.112 4,126.848 ↑ 1.0 1 125,056

Unique (cost=0.82..0.82 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=125,056)

18. 1,000.448 3,876.736 ↓ 3.0 3 125,056

Sort (cost=0.82..0.82 rows=1 width=32) (actual time=0.031..0.031 rows=3 loops=125,056)

  • Sort Key: (((opendoorrecord.opentime)::date)::text)
  • Sort Method: quicksort Memory: 25kB
19. 2,876.288 2,876.288 ↓ 11.0 11 125,056

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

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 60898
20. 0.000 1,125.504 ↓ 0.0 0 125,056

Subquery Scan on *SELECT* 2 (cost=0.57..0.81 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=125,056)

21. 250.112 1,125.504 ↓ 0.0 0 125,056

Limit (cost=0.57..0.80 rows=1 width=20) (actual time=0.009..0.009 rows=0 loops=125,056)

22. 875.392 875.392 ↓ 0.0 0 125,056

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=0 loops=125,056)

  • Index Cond: ((lockid = (lockkeyinfo.lockid)::text) AND (userid = (lockkeyinfo.keyholder)::text) AND (opentime < (((timezone('PRC'::text, now()) - '90 days'::interval))::date)::text))
  • Heap Fetches: 68
23. 2,225.890 2,225.890 ↑ 1.0 8 222,589

Index Scan using billrecord_lockid_idx on billrecord (cost=0.42..1.32 rows=8 width=43) (actual time=0.004..0.010 rows=8 loops=222,589)

  • Index Cond: ((lockid)::text = (lockkeyinfo.lockid)::text)
Planning time : 38.995 ms
Execution time : 15,201.576 ms