Skip site navigation (1) Skip section navigation (2)

Question about plan

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Postgresql General <pgsql-general(at)postgresql(dot)org>
Subject: Question about plan
Date: 2001-08-29 06:55:48
Message-ID: 20010829165548.A7842@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
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.

Responses

pgsql-general by date

Next:From: ShanmugasundaramDate: 2001-08-29 07:21:06
Subject: Regarding vacuumdb
Previous:From: Bruce MomjianDate: 2001-08-29 04:57:06
Subject: LinuxWorld BOF

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group