How come an aggreate like that has to use a sequential scan? I know
that PostgreSQL use to have to do a sequential scan for all aggregates,
but there was support added to version 8 so that aggregates would take
advantage of indexes. This is why
SELECT max(ts) AS ts
FROM asset_positions;
Uses an index on the ts column and only takes 50 milliseconds. When I
added the group by it would not use a multikey index or any other
index. Is there just no support for aggregates to use multikey
indexes? Sorry to be so pushy, but I just want to make sure I
understand why the above query can use an index and the following can't:
SELECT assetid, max(ts) AS ts
FROM asset_positions
GROUP BY assetid;
--
Graham Davis
Refractions Research Inc.
gdavis@xxxxxxxxxxxxxxx
Chris Browne wrote:
gdavis@xxxxxxxxxxxxxxx (Graham Davis) writes:
40 seconds is much too slow for this query to run and I'm assuming
that the use of an index will make it much faster (as seen when I
removed the GROUP BY clause). Any tips?
Assumptions are dangerous things.
An aggregate like this has *got to* scan the entire table, and given
that that is the case, an index scan is NOT optimal; a seq scan is.
An index scan is just going to be slower.
