explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TtBz

Settings
# exclusive inclusive rows x rows loops node
1. 79,116.523 79,116.523 ↑ 48.5 275 1

CTE Scan on activi_people (cost=174,983,567.04..174,983,833.70 rows=13,333 width=156) (actual time=79,103.194..79,116.523 rows=275 loops=1)

2.          

CTE dates_in_interest

3. 0.302 0.303 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.188..0.303 rows=31 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

5.          

CTE bucket

6. 0.521 0.521 ↑ 32.3 31 1

CTE Scan on dates_in_interest (cost=0.00..70.00 rows=1,000 width=128) (actual time=0.196..0.521 rows=31 loops=1)

7.          

CTE candidates_r2

8. 1,191.660 2,440.303 ↑ 4,984.9 17,099 1

Group (cost=85,474,604.21..90,191,831.99 rows=85,236,800 width=119) (actual time=2,318.891..2,440.303 rows=17,099 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid, bucket.*
9. 856.866 1,248.643 ↑ 22,730.3 20,753 1

Sort (cost=85,474,604.21..86,653,911.15 rows=471,722,778 width=119) (actual time=1,239.584..1,248.643 rows=20,753 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid, bucket.*
  • Sort Method: quicksort Memory: 6281kB
10. 90.180 391.777 ↑ 22,730.3 20,753 1

Nested Loop (cost=0.43..14,198,202.80 rows=471,722,778 width=119) (actual time=0.274..391.777 rows=20,753 loops=1)

11. 0.618 0.618 ↑ 32.3 31 1

CTE Scan on bucket (cost=0.00..20.00 rows=1,000 width=152) (actual time=0.205..0.618 rows=31 loops=1)

12. 300.979 300.979 ↑ 705.1 669 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord (cost=0.43..9,480.95 rows=471,723 width=51) (actual time=1.250..9.709 rows=669 loops=31)

  • Index Cond: (((opentime)::text >= bucket.r2_from) AND ((opentime)::text < bucket.r2_to))
  • Filter: (userid IS NOT NULL)
  • Rows Removed by Filter: 3
13.          

CTE candidates

14. 42.178 76,871.210 ↑ 14,454.3 5,897 1

Nested Loop (cost=0.56..70,107,268.00 rows=85,236,800 width=188) (actual time=2,319.056..76,871.210 rows=5,897 loops=1)

15. 10,809.793 10,809.793 ↑ 4,984.9 17,099 1

CTE Scan on candidates_r2 (cost=0.00..1,704,736.00 rows=85,236,800 width=188) (actual time=2,318.895..10,809.793 rows=17,099 loops=1)

16. 66,019.239 66,019.239 ↓ 0.0 0 17,099

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.56..0.78 rows=1 width=58) (actual time=3.860..3.861 rows=0 loops=17,099)

  • Index Cond: ((lockid = (candidates_r2.lockid)::text) AND (userid = (candidates_r2.userid)::text) AND (opentime >= (candidates_r2.bucket).r1_from) AND (opentime < (candidates_r2.bucket).r1_to))
  • Heap Fetches: 147
17.          

CTE activi_people

18. 12.014 79,116.299 ↑ 48.5 275 1

GroupAggregate (cost=13,831,524.01..14,684,392.01 rows=13,333 width=156) (actual time=79,103.192..79,116.299 rows=275 loops=1)

  • Group Key: candidates.lockid, candidates.userid
  • Filter: (count(DISTINCT candidates.bucket) >= 3)
  • Rows Removed by Filter: 1833
19. 4.941 79,104.285 ↑ 14,454.3 5,897 1

Sort (cost=13,831,524.01..14,044,616.01 rows=85,236,800 width=188) (actual time=79,103.120..79,104.285 rows=5,897 loops=1)

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 1759kB
20. 79,099.344 79,099.344 ↑ 14,454.3 5,897 1

CTE Scan on candidates (cost=0.00..1,704,736.00 rows=85,236,800 width=188) (actual time=2,319.058..79,099.344 rows=5,897 loops=1)

Planning time : 3.868 ms
Execution time : 79,129.050 ms