Re: Question about plan

hi,
Haven't you made any index on internetusage(starttime)? I think it would be
very usful.
At the moment I have the same internet accounting system with a log table of
about 500'000 records, and it is doing well even with pg v. 6.5.2. I have
indexed my table on userID,starttime.
If I were you , I would change my table so that it contains
starttime,stoptime which was indexed on start_time (if no userID needed).

HTH
Omid

>From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
>Reply-To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
>To: Postgresql General <pgsql-general(at)postgresql(dot)org>
>Subject: [GENERAL] Question about plan
>Date: Wed, 29 Aug 2001 16:55:48 +1000
>
>Basically, I have one table A with a set of start times and duration and
>another table B with just a set of times. What I want is a result that for
>each time in table B count the number of intervals in table A that it is
>in.
>
>The query is not so difficult but it's a large set. The plan looks like
>this:
>
>Aggregate (cost=17365.57..17671.74 rows=3062 width=28)
> -> Group (cost=17365.57..17595.20 rows=30618 width=28)
> -> Sort (cost=17365.57..17365.57 rows=30618 width=28)
> -> Nested Loop (cost=0.00..14718.41 rows=30618 width=28)
> -> Seq Scan on internetusage (cost=0.00..596.04
>rows=191 width=16)
> -> Seq Scan on times (cost=0.00..23.40 rows=1440
>width=12)
>
>The problem is that it is running out of memory (after using around 500MB).
>I imagine this is because it has to build the entire result in the sort
>step. Can someone tell me why it doesn't use a plan like this one?
>
>Aggregate
> -> Group
> -> Merge Join
> -> Sort
> -> Seq Scan on internetusage
> -> Sort
> -> Seq Scan on times
>
>This would use a fraction of the memory and in this case that would make it
>faster since it could start producing output faster. Using limit 1 has no
>effect. The version is 7.0.2
>
>If later versions fix it, let me know. Workarounds would be appreciated
>also. The actual query follows:
>
>select date_trunc('day',starttime) + minute as date,
> count(iuid)
>from times, internetusage
>where date_trunc('month',starttime) = '2001-07-01'
>and minute between starttime::time
> and starttime::time + (duration || 'seconds')::interval
>group by date, starttime, minute;
>--
>Martijn van Oosterhout <kleptog(at)svana(dot)org>
>http://svana.org/kleptog/
> > It would be nice if someone came up with a certification system that
> > actually separated those who can barely regurgitate what they crammed
>over
> > the last few weeks from those who command secret ninja networking
>powers.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp