Re: sequential scan on select distinct

You could try :

explain analyze select "land" from "customer_dim" group by "land";
It will be a lot faster but I can't make it use the index on my machine...

Example :

create table dummy as (select id, id%255 as number from a large table
with 1M rows);
so we have a table with 256 (0-255) disctinct "number" values.

--------------------------------------------------------------------------------
=> explain analyze select distinct number from dummy;
Unique (cost=69.83..74.83 rows=200 width=4) (actual
time=13160.490..14414.004 rows=255 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=4) (actual
time=13160.483..13955.792 rows=1000000 loops=1)
Sort Key: number
-> Seq Scan on dummy (cost=0.00..20.00 rows=1000 width=4)
(actual time=0.052..1759.145 rows=1000000 loops=1)
Total runtime: 14442.872 ms

=> Horribly slow because it has to sort 1M rows for the Unique.

--------------------------------------------------------------------------------
=> explain analyze select number from dummy group by number;
HashAggregate (cost=22.50..22.50 rows=200 width=4) (actual
time=1875.214..1875.459 rows=255 loops=1)
-> Seq Scan on dummy (cost=0.00..20.00 rows=1000 width=4) (actual
time=0.107..1021.014 rows=1000000 loops=1)
Total runtime: 1875.646 ms

=> A lot faster because it HashAggregates instead of sorting (but still
seq scan)

--------------------------------------------------------------------------------
Now :
create index dummy_idx on dummy(number);
Let's try again.
--------------------------------------------------------------------------------
explain analyze select distinct number from dummy;
Unique (cost=0.00..35301.00 rows=200 width=4) (actual
time=0.165..21781.732 rows=255 loops=1)
-> Index Scan using dummy_idx on dummy (cost=0.00..32801.00
rows=1000000 width=4) (actual time=0.162..21154.752 rows=1000000 loops=1)
Total runtime: 21782.270 ms

=> Index scan the whole table. argh. I should have ANALYZized.
--------------------------------------------------------------------------------
explain analyze select number from dummy group by number;
HashAggregate (cost=17402.00..17402.00 rows=200 width=4) (actual
time=1788.425..1788.668 rows=255 loops=1)
-> Seq Scan on dummy (cost=0.00..14902.00 rows=1000000 width=4)
(actual time=0.048..960.063 rows=1000000 loops=1)
Total runtime: 1788.855 ms
=> Still the same...
--------------------------------------------------------------------------------
Let's make a function :
The function starts at the lowest number and advances to the next number
in the index until they are all exhausted.

CREATE OR REPLACE FUNCTION sel_distinct()
RETURNS SETOF INTEGER
LANGUAGE plpgsql
AS '
DECLARE
pos INTEGER;
BEGIN
SELECT INTO pos number FROM dummy ORDER BY number ASC LIMIT 1;
IF NOT FOUND THEN
RAISE NOTICE ''no records.'';
RETURN;
END IF;

LOOP
RETURN NEXT pos;
SELECT INTO pos number FROM dummy WHERE number>pos ORDER BY number ASC
LIMIT 1;
IF NOT FOUND THEN
RETURN;
END IF;
END LOOP;
END;
';

explain analyze select * from sel_distinct();
Function Scan on sel_distinct (cost=0.00..12.50 rows=1000 width=4)
(actual time=215.472..215.696 rows=255 loops=1)
Total runtime: 215.839 ms

That's better !
--------------------------------------------------------------------------------
Why not use DESC instead of ASC ?

CREATE OR REPLACE FUNCTION sel_distinct()
RETURNS SETOF INTEGER
LANGUAGE plpgsql
AS '
DECLARE
pos INTEGER;
BEGIN
SELECT INTO pos number FROM dummy ORDER BY number DESC LIMIT 1;
IF NOT FOUND THEN
RAISE NOTICE ''no records.'';
RETURN;
END IF;

LOOP
RETURN NEXT pos;
SELECT INTO pos number FROM dummy WHERE number<pos ORDER BY number DESC
LIMIT 1;
IF NOT FOUND THEN
RETURN;
END IF;
END LOOP;
END;
';

explain analyze select * from sel_distinct();
Function Scan on sel_distinct (cost=0.00..12.50 rows=1000 width=4)
(actual time=13.500..13.713 rows=255 loops=1)
Total runtime: 13.857 ms

Hum hum ! Again, a lot better !
Index scan backwards seems a lot faster than index scan forwards. Why, I
don't know, but here you go from 15 seconds to 14 milliseconds...

I don't know WHY (oh why) postgres does not use this kind of strategy
when distinct'ing an indexed field... Anybody got an idea ?