explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5Ac

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 2,734.456 ↑ 1.0 20 1

Sort (cost=706,180.08..706,180.13 rows=20 width=492) (actual time=2,734.448..2,734.456 rows=20 loops=1)

  • Sort Key: (("*SELECT* 1".num)::numeric)
  • Sort Method: quicksort Memory: 71kB
2. 0.012 2,734.403 ↑ 1.0 20 1

Append (cost=706,019.61..706,179.65 rows=20 width=492) (actual time=2,733.285..2,734.403 rows=20 loops=1)

3. 0.011 2,733.714 ↑ 1.0 18 1

Subquery Scan on *SELECT* 1 (cost=706,019.61..706,020.60 rows=18 width=517) (actual time=2,733.283..2,733.714 rows=18 loops=1)

4. 0.015 2,733.703 ↑ 1.0 18 1

Subquery Scan on s (cost=706,019.61..706,020.38 rows=18 width=493) (actual time=2,733.280..2,733.703 rows=18 loops=1)

5. 0.018 2,733.688 ↑ 1.0 18 1

Limit (cost=706,019.61..706,020.20 rows=18 width=649) (actual time=2,733.278..2,733.688 rows=18 loops=1)

6. 0.441 2,733.670 ↑ 72.2 18 1

WindowAgg (cost=706,019.61..706,061.83 rows=1,299 width=649) (actual time=2,733.277..2,733.670 rows=18 loops=1)

7. 2.141 2,733.229 ↑ 72.2 18 1

Sort (cost=706,019.61..706,022.86 rows=1,299 width=489) (actual time=2,733.214..2,733.229 rows=18 loops=1)

  • Sort Key: f.coverage DESC NULLS LAST
  • Sort Method: quicksort Memory: 1920kB
8. 38.654 2,731.088 ↓ 1.0 1,300 1

GroupAggregate (cost=705,897.22..705,952.43 rows=1,299 width=489) (actual time=2,690.895..2,731.088 rows=1,300 loops=1)

  • Group Key: p.pretty, p.slug, p.image, p.phonenumber_internet, p.website, p.wikipedia, f.hoconums, f.stateabbr
9. 26.891 2,692.434 ↓ 3.9 5,122 1

Sort (cost=705,897.22..705,900.47 rows=1,299 width=515) (actual time=2,690.782..2,692.434 rows=5,122 loops=1)

  • Sort Key: p.pretty, p.slug, p.image, p.phonenumber_internet, p.website, p.wikipedia, f.hoconums, f.stateabbr
  • Sort Method: quicksort Memory: 3751kB
10. 3.555 2,665.543 ↓ 3.9 5,122 1

Nested Loop Left Join (cost=539.04..705,830.05 rows=1,299 width=515) (actual time=2.648..2,665.543 rows=5,122 loops=1)

11. 2.401 857.056 ↓ 1.3 1,629 1

Nested Loop Left Join (cost=197.87..262,588.51 rows=1,299 width=399) (actual time=1.044..857.056 rows=1,629 loops=1)

12. 3.489 267.453 ↓ 1.0 1,302 1

Nested Loop Left Join (cost=0.06..5,526.16 rows=1,299 width=393) (actual time=0.289..267.453 rows=1,302 loops=1)

13. 1.566 1.566 ↑ 1.0 1,299 1

Seq Scan on coverage_nationwide_consumer f (cost=0.00..144.50 rows=1,299 width=253) (actual time=0.011..1.566 rows=1,299 loops=1)

  • Filter: (techcode <> '{70}'::text[])
  • Rows Removed by Filter: 661
14. 174.066 262.398 ↓ 0.0 0 1,299

Bitmap Heap Scan on fixed_dec14_v1_providers p (cost=0.06..4.13 rows=1 width=630) (actual time=0.191..0.202 rows=0 loops=1,299)

  • Recheck Cond: (f.stateabbr && states)
  • Filter: ((f.hoconums && (hoconums)::text[]) AND (f.techcode && techcode))
  • Rows Removed by Filter: 366
  • Heap Blocks: exact=53054
15. 88.332 88.332 ↓ 94.0 376 1,299

Bitmap Index Scan on fixed_dec14_v1_providers_hoconums_states_idx1 (cost=0.00..0.06 rows=4 width=0) (actual time=0.068..0.068 rows=376 loops=1,299)

  • Index Cond: (f.stateabbr && states)
16. 2.604 587.202 ↓ 0.0 0 1,302

GroupAggregate (cost=197.81..197.87 rows=1 width=1,476) (actual time=0.451..0.451 rows=0 loops=1,302)

  • Group Key: pl.price, pl.channels, pl.download, pl.upload, pl.type, pl.name, pl.includes, pl.special, pl.marker, pl.bonuses, pl.keyfeatures, pl.datalimit, pl.extra, pl.full_disclaimer, pl.voice, pl.priceformonths, pl.techcode, pl.disclaimer, pl.contract
17. 13.020 584.598 ↓ 0.0 0 1,302

Sort (cost=197.81..197.81 rows=1 width=1,348) (actual time=0.449..0.449 rows=0 loops=1,302)

  • Sort Key: pl.price, pl.channels DESC, pl.download DESC, pl.upload DESC, pl.type, pl.name, pl.includes, pl.special, pl.marker, pl.bonuses, pl.keyfeatures, pl.datalimit, pl.extra, pl.full_disclaimer, pl.voice, pl.priceformonths, pl.techcode, pl.disclaimer, pl.contract
  • Sort Method: quicksort Memory: 25kB
18. 571.578 571.578 ↓ 0.0 0 1,302

Seq Scan on fixed_dec14_v1_plans pl (cost=0.00..197.80 rows=1 width=1,348) (actual time=0.420..0.439 rows=0 loops=1,302)

  • Filter: (visible AND (f.hoconums && (hoconums)::text[]) AND ((techcode)::text[] && f.techcode) AND ((type)::text = ANY ('{internet,bundle}'::text[])) AND ((f.stateabbr && states) OR ((((p.slug)::text = 'xfinity'::text) OR ((p.slug)::text = 'windstream'::text)) AND (states IS NULL))) AND (overridemaxaddown OR ((f.maxaddown >= (download)::double precision) AND ((minadspeed IS NULL) OR (f.maxaddown >= (minadspeed)::double precision)) AND ((maxadspeed IS NULL) OR (f.maxaddown < (maxadspeed)::double precision)))))
  • Rows Removed by Filter: 1259
19. 6.516 1,804.932 ↓ 3.0 3 1,629

Subquery Scan on transfer (cost=341.16..341.21 rows=1 width=116) (actual time=1.103..1.108 rows=3 loops=1,629)

20. 4.887 1,798.416 ↓ 3.0 3 1,629

Unique (cost=341.16..341.20 rows=1 width=119) (actual time=1.101..1.104 rows=3 loops=1,629)

21. 13.032 1,793.529 ↓ 3.0 3 1,629

Sort (cost=341.16..341.17 rows=1 width=119) (actual time=1.100..1.101 rows=3 loops=1,629)

  • Sort Key: (row_number() OVER (?)), transfer_1.techname, transfer_1.techcode, transfer_1.coverage, transfer_1.percentpop10, transfer_1.housing10, transfer_1.pop10, transfer_1.consumer, (CASE WHEN ((transfer_1.techname = 'DSL'::text) AND (transfer_1.maxaddown > '300'::double precision)) THEN '300'::double precision ELSE transfer_1.maxaddown END), (CASE WHEN ((transfer_1.techname = 'DSL'::text) AND (transfer_1.maxadup > '100'::double precision)) THEN '100'::double precision ELSE transfer_1.maxadup END), transfer_1.medianaddown, transfer_1.medianadup
  • Sort Method: quicksort Memory: 25kB
22. 8.145 1,780.497 ↓ 3.0 3 1,629

WindowAgg (cost=341.12..341.15 rows=1 width=119) (actual time=1.090..1.093 rows=3 loops=1,629)

23. 9.774 1,772.352 ↓ 3.0 3 1,629

Sort (cost=341.12..341.12 rows=1 width=103) (actual time=1.087..1.088 rows=3 loops=1,629)

  • Sort Key: transfer_1.percentpop10 DESC, transfer_1.maxaddown DESC
  • Sort Method: quicksort Memory: 25kB
24. 1,762.578 1,762.578 ↓ 3.0 3 1,629

Seq Scan on coverage_techname_nationwide_consumer transfer_1 (cost=0.00..341.11 rows=1 width=103) (actual time=0.584..1.082 rows=3 loops=1,629)

  • Filter: ((hoconums && f.hoconums) AND (stateabbr && f.stateabbr))
  • Rows Removed by Filter: 4471
25. 0.003 0.677 ↑ 1.0 2 1

Subquery Scan on *SELECT* 2 (cost=158.96..159.05 rows=2 width=492) (actual time=0.656..0.677 rows=2 loops=1)

26. 0.036 0.674 ↑ 1.0 2 1

GroupAggregate (cost=158.96..159.03 rows=2 width=632) (actual time=0.653..0.674 rows=2 loops=1)

  • Group Key: p_1.pretty, p_1.slug, p_1.image, p_1.phonenumber_internet, p_1.website, p_1.wikipedia
27. 0.052 0.638 ↓ 17.5 35 1

Sort (cost=158.96..158.96 rows=2 width=146) (actual time=0.630..0.638 rows=35 loops=1)

  • Sort Key: p_1.pretty, p_1.slug, p_1.image, p_1.phonenumber_internet, p_1.website, p_1.wikipedia
  • Sort Method: quicksort Memory: 31kB
28. 0.019 0.586 ↓ 17.5 35 1

Nested Loop Left Join (cost=86.05..158.95 rows=2 width=146) (actual time=0.397..0.586 rows=35 loops=1)

29. 0.010 0.051 ↑ 1.0 2 1

Bitmap Heap Scan on fixed_dec14_v1_providers p_1 (cost=20.02..26.72 rows=2 width=173) (actual time=0.049..0.051 rows=2 loops=1)

  • Recheck Cond: (hoconums && '{000010,000012}'::character varying[])
  • Heap Blocks: exact=2
30. 0.041 0.041 ↑ 1.0 2 1

Bitmap Index Scan on fixed_dec14_v1_providers_hoconums_states_idx1 (cost=0.00..20.02 rows=2 width=0) (actual time=0.041..0.041 rows=2 loops=1)

  • Index Cond: (hoconums && '{000010,000012}'::character varying[])
31. 0.058 0.516 ↓ 18.0 18 2

GroupAggregate (cost=66.03..66.10 rows=1 width=1,476) (actual time=0.227..0.258 rows=18 loops=2)

  • Group Key: pl_1.price, pl_1.channels, pl_1.download, pl_1.upload, pl_1.type, pl_1.name, pl_1.includes, pl_1.special, pl_1.marker, pl_1.bonuses, pl_1.keyfeatures, pl_1.datalimit, pl_1.extra, pl_1.full_disclaimer, pl_1.voice, pl_1.priceformonths, pl_1.techcode, pl_1.disclaimer, pl_1.contract
32. 0.186 0.458 ↓ 28.0 28 2

Sort (cost=66.03..66.04 rows=1 width=1,348) (actual time=0.222..0.229 rows=28 loops=2)

  • Sort Key: pl_1.price, pl_1.channels DESC, pl_1.download DESC, pl_1.upload DESC, pl_1.type, pl_1.name, pl_1.includes, pl_1.special, pl_1.marker, pl_1.bonuses, pl_1.keyfeatures, pl_1.datalimit, pl_1.extra, pl_1.full_disclaimer, pl_1.voice, pl_1.priceformonths, pl_1.techcode, pl_1.disclaimer, pl_1.contract
  • Sort Method: quicksort Memory: 30kB
33. 0.174 0.272 ↓ 28.0 28 2

Bitmap Heap Scan on fixed_dec14_v1_plans pl_1 (cost=28.09..66.02 rows=1 width=1,348) (actual time=0.060..0.136 rows=28 loops=2)

  • Recheck Cond: (p_1.hoconums && hoconums)
  • Filter: (visible AND (region IS NOT NULL) AND ((type)::text = ANY ('{internet,bundle}'::text[])))
  • Rows Removed by Filter: 46
  • Heap Blocks: exact=70
34. 0.098 0.098 ↓ 6.8 81 2

Bitmap Index Scan on fixed_dec14_v1_plans_hoconums_states_idx1 (cost=0.00..28.09 rows=12 width=0) (actual time=0.049..0.049 rows=81 loops=2)

  • Index Cond: (p_1.hoconums && hoconums)