explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

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

2. 0.009 5.317 ↑ 50.0 4 1

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

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

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

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

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

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

CTE contest_points

6. 0.957 1.094 ↑ 1.0 1,000 1

HashAggregate (cost=23.00..33.00 rows=1,000 width=21) (actual time=0.756..1.094 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.006..0.137 rows=1,000 loops=1)

8.          

CTE cd_assignment

9. 0.356 0.760 ↑ 1.0 935 1

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

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

11. 1.055 1.055 ↑ 1.0 935 1

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

12. 0.309 3.078 ↑ 1.0 1,000 1

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

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

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

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

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

15. 0.316 0.574 ↑ 1.0 1,000 1

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

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

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

Planning time : 0.695 ms
Execution time : 5.492 ms