explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.017 5.361 ↑ 50.0 4 1

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

2. 0.017 5.344 ↑ 50.0 4 1

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

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

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

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

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

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

CTE contest_points

6. 0.917 1.045 ↑ 1.0 1,000 1

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

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

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

8.          

CTE cd_assignment

9. 0.357 0.796 ↑ 1.0 935 1

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

10. 0.439 0.439 ↑ 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.034..0.439 rows=1,021 loops=1)

11. 1.098 1.098 ↑ 1.0 935 1

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

12. 0.300 3.044 ↑ 1.0 1,000 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
13. 0.677 2.744 ↑ 1.0 1,000 1

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

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

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

15. 0.318 0.571 ↑ 1.0 1,000 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
16. 0.253 0.253 ↑ 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.253 rows=1,000 loops=1)

Planning time : 0.718 ms
Execution time : 5.563 ms