The planner is smarter with GROUP BY than with DISTINCT, so you can
rewrite your query as the following, whihc will probaly use a
HashAggregate, and be a lot faster :
SELECT service_id FROM five_min_stats_200408 GROUP BY service_id;
This won't avoid the Seq Scan however.
If you know you have few different service_ids, you can code a plpgsql
function which does this :
SELECT INTO value service_id FROM five_min_stats_200408 ORDER BY
service_id ASC LIMIT 1;
WHILE FOUND
RETURN NEXT value
SELECT INTO value service_id FROM five_min_stats_200408 WHERE
service_is>value ORDER BY service_id ASC LIMIT 1;
END WHILE
Basically it skips from one value to the next using your index, and
returns them as they come. You'll get one indexed SELECT by distinct
value. If you have, say 100 distinct values in 1M rows it'll be many
orders of magniude faster.
> estat=> explain analyze select distinct(service_id) from
> five_min_stats_200408;
> QUERY PLAN
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ----
> Unique (cost=13578354.70..13894902.76 rows=726 width=12) (actual
> time=1227906.271..1282110.055 rows=879 loops=1)
> -> Sort (cost=13578354.70..13736628.73 rows=63309612 width=12)
> (actual time=1227906.266..1255961.318 rows=63359396 loops=1)
> Sort Key: service_id
> -> Seq Scan on five_min_stats_200408 (cost=0.00..1668170.12
> rows=63309612 width=12) (actual time=0.061..80398.222 rows=63359396
> loops=1)
> Total runtime: 1284212.556 ms
> (5 rows)
>
> Time: 1284213.359 ms
>
