explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2xqq

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,762.899 ↑ 40.0 5 1

WindowAgg (cost=106,566.17..106,569.67 rows=200 width=80) (actual time=1,762.891..1,762.899 rows=5 loops=1)

2. 0.028 1,762.888 ↑ 40.0 5 1

Sort (cost=106,566.17..106,566.67 rows=200 width=72) (actual time=1,762.887..1,762.888 rows=5 loops=1)

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

HashAggregate (cost=106,556.02..106,558.52 rows=200 width=72) (actual time=1,762.857..1,762.860 rows=5 loops=1)

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

Hash Right Join (cost=99,156.96..101,306.02 rows=300,000 width=97) (actual time=1,524.370..1,648.597 rows=300,000 loops=1)

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

CTE contest_points

6. 460.023 818.012 ↓ 1.3 274,805 1

GroupAggregate (cost=0.43..73,436.63 rows=216,450 width=21) (actual time=0.033..818.012 rows=274,805 loops=1)

  • Group Key: event_attempts.phone
7. 357.989 357.989 ↑ 1.0 1,650,741 1

Index Scan using event_attempts_phone on event_attempts (cost=0.43..63,017.95 rows=1,650,835 width=17) (actual time=0.027..357.989 rows=1,650,741 loops=1)

8.          

CTE cd_assignment

9. 0.337 0.780 ↑ 1.0 935 1

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

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

11. 1.198 1.198 ↑ 1.0 935 1

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

12. 98.403 1,523.837 ↑ 1.0 300,000 1

Hash (cost=20,458.19..20,458.19 rows=300,000 width=14) (actual time=1,523.837..1,523.837 rows=300,000 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2797kB
13. 288.121 1,425.434 ↑ 1.0 300,000 1

Hash Right Join (cost=10,419.00..20,458.19 rows=300,000 width=14) (actual time=166.289..1,425.434 rows=300,000 loops=1)

  • Hash Cond: ((cp.phone)::text = (c.phone)::text)
14. 971.208 971.208 ↓ 1.3 274,805 1

CTE Scan on contest_points cp (cost=0.00..4,329.00 rows=216,450 width=40) (actual time=0.035..971.208 rows=274,805 loops=1)

15. 101.048 166.105 ↑ 1.0 300,000 1

Hash (cost=4,911.00..4,911.00 rows=300,000 width=19) (actual time=166.105..166.105 rows=300,000 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2380kB
16. 65.057 65.057 ↑ 1.0 300,000 1

Seq Scan on contestants c (cost=0.00..4,911.00 rows=300,000 width=19) (actual time=0.040..65.057 rows=300,000 loops=1)

Planning time : 0.635 ms
Execution time : 1,764.254 ms