The asset_positions table has about 1.7 million rows, and this query
takes over 40 seconds to do a sequential scan. Initially I was trying
to get the original query:
SELECT assetid, max(ts) AS ts
FROM asset_positions
GROUP BY assetid;
to use the multikey index since I read that PostgreSQL 8 added support
for aggregates to use indexes. However, the GROUP BY was causing the query
plan to not use any index (removing the GROUP by allowed the query to
use the ts index and it took only 50 ms to run). Since I need the query
to find the max time
for EACH asset, I can't just drop the GROUP BY from my query. So I was
trying some alternate ways of writing the query (as described in the
below email) to
force the use of one of these indexes.
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?
Graham.
Chris Browne wrote:
>gdavis@refractions.net (Graham Davis) writes:
>
>
>>Adding DESC to both columns in the SORT BY did not make the query use
>>the multikey index. So both
>>
>>SELECT DISTINCT ON (assetid) assetid, ts
>>FROM asset_positions ORDER BY assetid, ts DESC;
>>
>>and
>>
>>SELECT DISTINCT ON (assetid) assetid, ts
>>FROM asset_positions ORDER BY assetid DESC, ts DESC;
>>
>>use the same query plans and both do sequential scans without using
>>either the (assetid, ts) or (ts) indexes. Any other ideas on how to
>>make this query use an index? Thanks,
>>
>>
>
>Why do you want to worsen performance by forcing the use of an index?
>
>You are reading through the entire table, after all, and doing so via
>a sequential scan is normally the fastest way to do that. An index
>scan would only be more efficient if you don't have enough space in
>memory to store all assetid values.
>
>
--
Graham Davis
Refractions Research Inc.
gdavis@refractions.net
