Re: select distinct and index usage

On Mon, 7 Apr 2008 19:42:02 +0200
Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> > explain analyze select distinct datestamp from vals;
> > QUERY
> > PLAN
> > ----------------------------------------------------------------------
> > -------------------------------------------------------------------
> > Unique (cost=15003047.47..15380004.83 rows=4263 width=4) (actual
> > time=649599.159..721671.595 rows=4252 loops=1)
> > -> Sort (cost=15003047.47..15191526.15 rows=75391472 width=4)
> > (actual time=649599.157..694392.602 rows=75391476 loops=1)
> > Sort Key: datestamp
> > Sort Method: external merge Disk: 1178592kB
> > -> Seq Scan on vals (cost=0.00..1531261.72 rows=75391472
> > width=4) (actual time=9.104..93130.468 rows=75391476 loops=1)
> > Total runtime: 722379.434 ms
>
> Wow, great estimates! The planner obviously knows how your data is
> structured. So much for the bad planner estimation scenario...
>
> I haven't seen this "external merge Disk"-sort method before, maybe
> it's new in 8.3, but it doesn't look promising for query

I have to double check but I think that means he overflowed his work
mem and is sorting on disk. Try increasing workmem for the query.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate