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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
@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
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

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
I think @Sandeep's question is a bit different: why is there "Sort" step at all? pid is already sorted. So, ideally, this particular query is a very simple one-pass algorithm: as you go through a particular pid, you count all did. Once you see a next pid, you are done with this group -- just output current count. Since original pid were sorted, your groups will be automatically sorted as well since they will be produced in the same sequence as original pids. – akhmed May 29 '15 at 23:14
Yes, you are right about the question. The problem is, that the table is sorted on pid only when the CLUSTER operation finishes - all subsequent changes will introduce disorder, as Craig described in his answer. – dezso May 30 '15 at 12:23
I see. I think proc sql engine from SAS offers an excellent compromise: they have a user-specified sortedby=pid flag that notifies the query optimizer about data being already pre-sorted. (A user can lie about sorting but if the user lies and the data is not sorted then the query would return a run-time error upon finding the first out-of-order instance). – akhmed May 31 '15 at 0:16

(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. – swasheck Jul 26 '13 at 5:04
@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

Your Answer


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.