explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.012 5.319 ↑ 50.0 4 1

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

2. 0.012 5.307 ↑ 50.0 4 1

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

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

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

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

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

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

CTE contest_points

6. 0.867 1.000 ↑ 1.0 1,000 1

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

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

8.          

CTE cd_assignment

9. 0.388 0.803 ↑ 1.0 935 1

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

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

11. 1.106 1.106 ↑ 1.0 935 1

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

12. 0.310 3.003 ↑ 1.0 1,000 1

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

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

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

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

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

15. 0.322 0.579 ↑ 1.0 1,000 1

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

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

Planning time : 0.700 ms
Execution time : 5.488 ms