explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LN4O : Optimization for: Optimization for: Optimization for: cd_leaderboard; plan #jCw5; plan #Y9O2; plan #sGs7

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.018 5.367 ↑ 50.0 4 1

WindowAgg (cost=206.43..209.93 rows=200 width=80) (actual time=5.358..5.367 rows=4 loops=1)

2. 0.011 5.349 ↑ 50.0 4 1

Sort (cost=206.43..206.93 rows=200 width=72) (actual time=5.349..5.349 rows=4 loops=1)

  • Sort Key: (sum(cp.total_points)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.521 5.338 ↑ 50.0 4 1

HashAggregate (cost=196.29..198.79 rows=200 width=72) (actual time=5.333..5.338 rows=4 loops=1)

  • Group Key: COALESCE(cda.cd, 'watchers'::character varying)
4. 0.679 4.817 ↑ 1.0 1,000 1

Hash Right Join (cost=144.78..178.38 rows=1,023 width=97) (actual time=3.075..4.817 rows=1,000 loops=1)

  • Hash Cond: ((cda.zcta5)::text = (c.zip5)::text)
5.          

CTE contest_points

6. 0.912 1.049 ↑ 1.0 1,000 1

HashAggregate (cost=23.00..33.00 rows=1,000 width=21) (actual time=0.736..1.049 rows=1,000 loops=1)

  • Group Key: event_attempts.phone
7. 0.137 0.137 ↑ 1.0 1,000 1

Seq Scan on event_attempts (cost=0.00..18.00 rows=1,000 width=17) (actual time=0.007..0.137 rows=1,000 loops=1)

8.          

CTE cd_assignment

9. 0.369 0.793 ↑ 1.0 935 1

Unique (cost=0.28..47.14 rows=935 width=9) (actual time=0.016..0.793 rows=935 loops=1)

10. 0.424 0.424 ↑ 1.0 1,021 1

Index Scan using zcta_cd116_zcta5 on zcta_cd116 (cost=0.28..44.59 rows=1,021 width=9) (actual time=0.015..0.424 rows=1,021 loops=1)

11. 1.092 1.092 ↑ 1.0 935 1

CTE Scan on cd_assignment cda (cost=0.00..18.70 rows=935 width=64) (actual time=0.018..1.092 rows=935 loops=1)

12. 0.319 3.046 ↑ 1.0 1,000 1

Hash (cost=52.14..52.14 rows=1,000 width=14) (actual time=3.046..3.046 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
13. 0.654 2.727 ↑ 1.0 1,000 1

Hash Right Join (cost=29.50..52.14 rows=1,000 width=14) (actual time=1.328..2.727 rows=1,000 loops=1)

  • Hash Cond: ((cp.phone)::text = (c.phone)::text)
14. 1.500 1.500 ↑ 1.0 1,000 1

CTE Scan on contest_points cp (cost=0.00..20.00 rows=1,000 width=40) (actual time=0.738..1.500 rows=1,000 loops=1)

15. 0.334 0.573 ↑ 1.0 1,000 1

Hash (cost=17.00..17.00 rows=1,000 width=19) (actual time=0.572..0.573 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
16. 0.239 0.239 ↑ 1.0 1,000 1

Seq Scan on contestants c (cost=0.00..17.00 rows=1,000 width=19) (actual time=0.011..0.239 rows=1,000 loops=1)

Planning time : 0.738 ms
Execution time : 5.528 ms