explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jCw5 : cd_leaderboard

Settings

Optimization(s) for this plan:

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

WindowAgg (cost=231.63..235.13 rows=200 width=80) (actual time=6.148..6.158 rows=4 loops=1)

2. 0.013 6.141 ↑ 50.0 4 1

Sort (cost=231.63..232.13 rows=200 width=72) (actual time=6.141..6.141 rows=4 loops=1)

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

HashAggregate (cost=221.49..223.99 rows=200 width=72) (actual time=6.123..6.128 rows=4 loops=1)

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

Hash Right Join (cost=169.98..203.58 rows=1,023 width=97) (actual time=4.281..5.671 rows=1,000 loops=1)

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

CTE contest_points

6. 0.903 1.037 ↑ 1.0 1,000 1

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

  • Group Key: event_attempts.phone
7. 0.134 0.134 ↑ 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.134 rows=1,000 loops=1)

8.          

CTE cd_assignment

9. 0.361 1.680 ↑ 1.0 935 1

Unique (cost=67.24..72.34 rows=935 width=9) (actual time=1.234..1.680 rows=935 loops=1)

10. 1.093 1.319 ↑ 1.0 1,021 1

Sort (cost=67.24..69.79 rows=1,021 width=9) (actual time=1.232..1.319 rows=1,021 loops=1)

  • Sort Key: zcta_cd116.zcta5
  • Sort Method: quicksort Memory: 72kB
11. 0.226 0.226 ↑ 1.0 1,021 1

Seq Scan on zcta_cd116 (cost=0.00..16.21 rows=1,021 width=9) (actual time=0.008..0.226 rows=1,021 loops=1)

12. 1.959 1.959 ↑ 1.0 935 1

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

13. 0.314 3.028 ↑ 1.0 1,000 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
14. 0.664 2.714 ↑ 1.0 1,000 1

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

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

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

16. 0.308 0.566 ↑ 1.0 1,000 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
17. 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.012..0.258 rows=1,000 loops=1)

Planning time : 0.495 ms
Execution time : 6.325 ms