Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid:

select count(did), pid 
from  T
group by pid

Here is the query plan:

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 already has clustered index on pid.

Why doesn't Postgres simply scan the table and compute the group by? Why does it need to sort on pid again?

How can I force Postgres to use the clustered index for the group by?

share|improve this question
3  
I didn't know pg had "clustered indexes" (but I don't know it well). Could you describe exactly what indexes you have defined, and how you clustered, to make sure everyone knows exactly what you have? (And sorry if I'm completely missing the point due to my lack of knowledge of Postgres - just ignore this comment then :-) ) –  Mat Jul 25 '13 at 15:30
2  
@Mat you are completely right - you can cluster a table on an index but it is a one-time operation. –  dezso Jul 25 '13 at 15:40
2  
Also, please post the result of the EXPLAIN ANALYZE which shows the actual times and row numbers. Until then I'll leave an answer which may become useless later. –  dezso Jul 25 '13 at 15:42
    
I created clustered index using the following two commands: –  Sandeep Jul 25 '13 at 23:14
    
@Mat At a guess, they're regular secondary indexes of the heap (one of which might be unique and/or a PK), with one of them having been used to CLUSTER the table. This does not create a clustered index. –  Craig Ringer Jul 26 '13 at 7:09
add comment

2 Answers 2

As you have no WHERE clause, you (the query, that is) must scan the whole table to be able to compute the aggregate. Once it has all the data, it is much easier to sort them directly than to go to an index and match the rows and return them in order. This latter would involve further I/O which is slow compared to operation in the memory.

If you were on PostgreSQL 9.2 (or above) and you had an index on (pid, did) then the optimizer might have chosen an index scan instead.

share|improve this answer
add comment

(Elaborating on @dezso's answer, which should be accepted as correct in preference to this one):

However, the table already has clustered index on pid.

No, it doesn't, because PostgreSQL (at least in 9.3 and below) does not have clustered indexes ("index-oriented tables" in the language of some other DB vendors). All tables in PostgreSQL are heaps with secondary indexes.

You can CLUSTER on an index to sort the heap according to the index, but it's a one-time thing. Pg doesn't attempt to maintain that ordering with subsequent updates/inserts, so it cannot rely on the table being in that order. See the docs:

"When a table is CLUSTERed, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order."

The closest thing Pg offers to index-only tables is support for index-only scans over secondary indexes in 9.2 and above. If there was an index on (pid, did) and the visibility map was sufficiently solid (ie: not too high a churn rate, autovacuum running frequently enough) then Pg might choose to perform an index-only scan. It would fetch most of the data from the secondary index and only goes to the heap for a minority of rows where it needs to check visibility information.

So: Consider upgrading to 9.2 and creating an index on (pid, did).

share|improve this answer
    
Both you and @dezso are both much better at Postgres than I will ever hope to be., and I'm not trying to contradict but nuance. I was under the impression that you could reorganize the table repeatedly with CLUSTER. This would be a maintenance nightmare, though. postgresql.org/docs/9.1/static/sql-cluster.html –  swasheck Jul 26 '13 at 5:04
1  
@swasheck Sure you can; I think "one time" is meant as "not ongoing" rather than "only ever once". The point is that under update/insert/delete load it doesn't stay clustered. –  Craig Ringer Jul 26 '13 at 7:08
    
Thanks for the elaboration :) –  dezso Jul 26 '13 at 7:22
    
Ah. Ok. Thanks, @Craig –  swasheck Jul 26 '13 at 12:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.