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.

I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...

select run_id, count(*) from result group by run_id;

I have an index defined on the run_id column.

How can I speed this up?

share|improve this question
3  
Of course it's using a sequential scan, because you are reading all rows from the table. –  a_horse_with_no_name Jan 28 '13 at 16:14
4  
In PostgreSQL 9.2 it could use an index (and presumably be faster). Your version is quite old, maybe this is the time for upgrading? –  dezso Jan 28 '13 at 16:17
    
The number of unique run_id values would be under 30k. There's no way to speed up collection of this data? I will consider upgrading. –  FogleBird Jan 28 '13 at 16:22
3  
The number of unique run_ids does not matter. Postgres has to read all the rows in order to calculate the unique values. The only way to speed this is up is to use faster harddisk or upgrading to a 9.2 which might be able to use the existing index to calculate the result. (8.3 will be out of support next month!) –  a_horse_with_no_name Jan 28 '13 at 17:43

3 Answers 3

So why does Postgres 9.2 still show a sequential scan? I quote the Postgres Wiki:

Is "count(*)" much faster now?

A traditional complaint made of PostgreSQL, generally when comparing it unfavourably with MySQL (at least when using the MyIsam storage engine, which doesn't use MVCC) has been "count(*) is slow". Index-only scans can be used to satisfy these queries without there being any predicate to limit the number of rows returned, and without forcing an index to be used by specifying that the tuples should be ordered by an indexed column. However, in practice that isn't particularly likely.

It is important to realise that the planner is concerned with minimising the total cost of the query. With databases, the cost of I/O typically dominates. For that reason, "count(*) without any predicate" queries will only use an index-only scan if the index is significantly smaller than its table. This typically only happens when the table's row width is much wider than some indexes'.

Emphasis mine.
There is hardly anything to gain from an index scan here, as long as your talbe isn't bloated with more (big) columns.

share|improve this answer
    
An index scan can improve the performance here because of the part you emphasized. If run_id is the only column in the index, a table row will very likely be much wider than then an index "row" (unless the table only has two or three columns) and thus scanning the index will require less I/O compared to a table scan (because it consists of fewer database blocks). To know this for sure we would need to see the table definition. –  a_horse_with_no_name Feb 9 '13 at 9:52

I upgraded to PostgreSQL 9.2 overnight.

EXPLAIN still shows the query using a sequential scan, but the query seems to run much faster now... under 10 seconds. This will be sufficient for my needs.

Thanks to everyone for the help in the comments.

share|improve this answer
1  
From my experience, the new seq scan is only used if analyze (vacuum?) has been run more than once (and if the table isn't being updated too often). It seems there is still some work to be done to be on-par with Oracle and the others when it comes to index only scans. –  a_horse_with_no_name Jan 29 '13 at 18:03
    
@a_horse_with_no_name: This is largely beside the point here. Index-only scans will rarely be useful at all for a query like this. I added an answer with a big quote from the Postgres Wiki. –  Erwin Brandstetter Feb 4 '13 at 22:10

You are using 2 most slow functions in SQL, i.e. count and group by try this:

COUNT(DISTINCT run_id) FROM result

you can try making sub-queries too...

share|improve this answer
5  
GROUP BY is not a function and your query is not equivalent to the OP's. Yours will return exactly 1 row. OP's query will return 30k rows (as many as the distinct run_ids.) –  ypercube Jan 28 '13 at 21:47

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.