3

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?

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.

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

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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