Re: select distinct and index usage

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>
Cc: Gregory Stark <stark(at)enterprisedb(dot)com>, Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>, pgsql-general(at)postgresql(dot)org
Subject: Re: select distinct and index usage
Date: 2008-04-08 01:01:24
Message-ID: 20080408010124.GG5095@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

David Wilson escribió:

> explain analyze select datestamp from vals group by datestamp;
> QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=1719740.40..1719783.03 rows=4263 width=4)
> (actual time=120192.018..120193.930 rows=4252 loops=1)
> -> Seq Scan on vals (cost=0.00..1531261.72 rows=75391472 width=4)
> (actual time=17.441..66807.429 rows=75391476 loops=1)
> Total runtime: 120195.144 ms

> Still doing the sequential scan on the table, but at least it's
> avoiding the expensive disk merge sort. It still seems as if I ought
> to be able to coax it into using an index for this type of query,
> though- especially since it's using one on the other table. Is there
> perhaps some way to reformulate the index in such a way as to make it
> more useful to the planner?

Hmm, why do you think an indexscan would be faster? Since there's no
sort step involved, a seqscan as input for the HashAggregate is actually
better than an indexscan, because there's no need for the index entries
at all.

If you want to test, try SET enable_seqscan TO 0 and then rerun the
explain analyze. My bet is that it will use the index, and it will take
longer.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Colin Wetherbee 2008-04-08 01:08:15 Re: Most Occurring Value
Previous Message David Wilson 2008-04-08 00:54:38 Re: select distinct and index usage