Hi,
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
[EMAIL PROTECTED] (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
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
Also, the multikey index of (assetid, ts) would already be sorted and
that is why using such an index in this case is
faster than doing a sequential scan that does the sorting afterwards.
Graham.
Chris Browne wrote:
[EMAIL PROTECTED] (Graham Davis) writes:
Adding DESC to both columns in
[EMAIL PROTECTED] (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
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;
On Tue, Oct 03, 2006 at 12:13:43 -0700,
Graham Davis [EMAIL PROTECTED] wrote:
Also, the multikey index of (assetid, ts) would already be sorted and
that is why using such an index in this case is
faster than doing a sequential scan that does the sorting afterwards.
That isn't necessarily
Graham Davis [EMAIL PROTECTED] writes:
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.
Not in a GROUP BY
Thanks Tom, that explains it and makes sense. I guess I will have to
accept this query taking 40 seconds, unless I can figure out another way
to write it so it can use indexes. If there are any more syntax
suggestions, please pass them on. Thanks for the help everyone.
Graham.
Tom Lane
Have you looked into a materialized view sort of approach? You could
create a table which had assetid as a primary key, and max_ts as a
column. Then use triggers to keep that table up to date as rows are
added/updated/removed from the main table.
This approach would only make sense if there
The summary table approach maintained by triggers is something we are
considering, but it becomes a bit more complicated to implement.
Currently we have groups of new positions coming in every few seconds or
less. They are not guaranteed to be in order. So for instance, a group
of positions
Hmmm. How many distinct assetids are there?
-- Mark Lewis
On Tue, 2006-10-03 at 14:23 -0700, Graham Davis wrote:
The summary table approach maintained by triggers is something we are
considering, but it becomes a bit more complicated to implement.
Currently we have groups of new positions
Not many. It fluctuates, but there are usually only ever a few hundred
at most. Each assetid has multi-millions of positions though.
Mark Lewis wrote:
Hmmm. How many distinct assetids are there?
-- Mark Lewis
On Tue, 2006-10-03 at 14:23 -0700, Graham Davis wrote:
The summary table
A few hundred is quite a lot for the next proposal and it's kind of an
ugly one, but might as well throw the idea out since you never know.
Have you considered creating one partial index per assetid? Something
along the lines of CREATE INDEX asset_index_N ON asset_positions(ts)
WHERE assetid=N?
Mark Lewis [EMAIL PROTECTED] writes:
Have you considered creating one partial index per assetid? Something
along the lines of CREATE INDEX asset_index_N ON asset_positions(ts)
WHERE assetid=N? I'd guess that the planner probably wouldn't be smart
enough to use the partial indexes unless you
15 matches
Mail list logo
