On Thu, Mar 24, 2005 at 01:07:39PM -0600, Matthew Nuzum wrote:
> I've tried `nice psql` in the past and I don't think that had much impact,
> but I haven't tried it on this query.
On linux, nice will only help if the query is CPU-bound. On FreeBSD,
nice affects I/O scheduling, as well as CPU, so i
"Matthew Nuzum" <[EMAIL PROTECTED]> writes:
> Thanks. Other than avoiding using too much sort mem, is there anything else
> I can do to ensure this query doesn't starve other processes for resources?
Not a lot.
> Doing the explain analyze only increases my server load by 1 and seems to
> readily
> I would strongly suggest doing the min and max calculations together:
>
> select groupid, min(col), max(col) from ...
>
> because if you do them in two separate queries 90% of the effort will be
> duplicated.
>
> regards, tom lane
Thanks. Other than avoiding using t
"Matthew Nuzum" <[EMAIL PROTECTED]> writes:
> I believe there are about 40,000,000 rows, I expect there to be about
> 10,000,000 groups. PostgreSQL version is 7.3.2 and the sort_mem is at the
> default setting.
Okay. I doubt that the nearby suggestion to convert the min()s to
indexscans will help
> How many rows in usage_access?
Oh, I just got my explain analyze:
QUERY
PLAN
---
> How many rows in usage_access? How many groups do you expect?
> (Approximate answers are fine.) What PG version is this, and
> what's your sort_mem setting?
>
> regards, tom lane
I believe there are about 40,000,000 rows, I expect there to be about
10,000,000 groups. Po
"Matthew Nuzum" <[EMAIL PROTECTED]> writes:
> Here is the query (BTW, there will be a corresponding "max" version of this
> query as well):
> INSERT INTO usage_sessions_min (accountid,atime,sessionid)
> select accountid, min(atime) as atime, sessionid from usage_access
> group by accountid,session
while you weren't looking, Matthew Nuzum wrote:
> select accountid, min(atime) as atime, sessionid from usage_access
> group by accountid,sessionid;
Try something along the lines of:
select ua.accountid
, (select atime
from usage_access
where sessionid = ua.sessionid
