explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IivR

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 73.502 ↑ 40.0 5 1

WindowAgg (cost=7,936.39..7,939.89 rows=200 width=80) (actual time=73.490..73.502 rows=5 loops=1)

2. 0.020 73.481 ↑ 40.0 5 1

Sort (cost=7,936.39..7,936.89 rows=200 width=72) (actual time=73.480..73.481 rows=5 loops=1)

  • Sort Key: (sum(cp.total_points)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 5.803 73.461 ↑ 40.0 5 1

HashAggregate (cost=7,926.24..7,928.74 rows=200 width=72) (actual time=73.455..73.461 rows=5 loops=1)

  • Group Key: COALESCE(cda.cd, 'watchers'::character varying)
4. 10.684 67.658 ↑ 6.0 10,000 1

Hash Right Join (cost=4,848.17..6,875.19 rows=60,060 width=97) (actual time=13.006..67.658 rows=10,000 loops=1)

  • Hash Cond: ((cp.phone)::text = (c.phone)::text)
5.          

CTE contest_points

6. 22.312 38.425 ↑ 6.6 9,135 1

GroupAggregate (cost=0.29..4,373.76 rows=60,060 width=21) (actual time=0.027..38.425 rows=9,135 loops=1)

  • Group Key: event_attempts.phone
7. 16.113 16.113 ↑ 1.1 55,108 1

Index Scan using event_attempts_phone on event_attempts (cost=0.29..3,472.86 rows=60,060 width=17) (actual time=0.018..16.113 rows=55,108 loops=1)

8.          

CTE cd_assignment

9. 0.361 0.813 ↑ 1.0 935 1

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

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

11. 44.013 44.013 ↑ 6.6 9,135 1

CTE Scan on contest_points cp (cost=0.00..1,201.20 rows=60,060 width=40) (actual time=0.030..44.013 rows=9,135 loops=1)

12. 3.408 12.961 ↓ 1.1 10,000 1

Hash (cost=315.27..315.27 rows=8,960 width=45) (actual time=12.961..12.961 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 572kB
13. 2.963 9.553 ↓ 1.1 10,000 1

Hash Right Join (cost=265.60..315.27 rows=8,960 width=45) (actual time=5.604..9.553 rows=10,000 loops=1)

  • Hash Cond: ((cda.zcta5)::text = (c.zip5)::text)
14. 1.083 1.083 ↑ 1.0 935 1

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

15. 3.135 5.507 ↓ 1.1 10,000 1

Hash (cost=153.60..153.60 rows=8,960 width=19) (actual time=5.506..5.507 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 627kB
16. 2.372 2.372 ↓ 1.1 10,000 1

Seq Scan on contestants c (cost=0.00..153.60 rows=8,960 width=19) (actual time=0.014..2.372 rows=10,000 loops=1)

Planning time : 0.637 ms
Execution time : 74.179 ms