group by query plan on already clustered index

From: Sandeep Gupta <gupta(dot)sandeep(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: group by query plan on already clustered index
Date: 2013-07-25 15:01:24
Message-ID: CAAywg7vw98Zk5c8TCjhU98moB2Lugq+=GS=Hv_Pgs6cmpD3w9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On a table T with two fields, f1 and f2, the sql command

select count(f2), f1
from T
group by f1

result is seq scan followed by a sort on f1 (see the query plan below):

GroupAggregate (cost=21566127.88..22326004.09 rows=987621 width=8)
-> Sort (cost=21566127.88..21816127.88 rows=100000000 width=8)
Sort Key: pid
-> Seq Scan on tc (cost=0.00..1442478.00 rows=100000000 width=8)

However, the table is already has clustered index on f1. My question is
why doesn't
postgres simply scan the table and compute the group? Why it needs to sort
on f1 again?
How can I force postgres to use the clustered index for group by?

Thanks.
Sandeep

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2013-07-25 15:09:11 Re: Why are stored procedures looked on so negatively?
Previous Message Steve Atkins 2013-07-25 14:10:45 Re: Why are stored procedures looked on so negatively?