Re: Very long running query

On Tue, Apr 10, 2001 at 11:38:49AM -0400, Joel Burton wrote:
> On 10 Apr 2001, Konstantinos Agouros wrote:
>
> > Hi,
> >
> > I have a query running for 97hours now and I am wondering if this can be made
> > any faster.
> > The Query is:
> > insert into dailyreport select timestamp(date '1-8-2001'),a.category,'Observed',sum(b.count) as count from
websensebycata, netscapereduce b where a.url = b.url and a.action='Observed' and a.datum='1-8-2001' and
b.datum='1-8-2001'and not exists (select url from urlcounts where urlcounts.url=b.url) group by a.category; 
> >
> > The tables websensebycat and netscapereduce do have indices on it.
> > Netscapereduce has 60020 entries and websensebycat has around 6000 entries.
> > Urlcounts has 55 entries. The whole thing is running postgres 7.1RC2.
> > Anybody has a tip to accelerate this. The whole thing is running on a E250 with
> > 64Bit Solaris7.
>
> what does explain command show about your query?
NOTICE:  QUERY PLAN:

Subquery Scan *SELECT*  (cost=0.02..0.03 rows=1 width=40)
  ->  Aggregate  (cost=0.02..0.03 rows=1 width=40)
        ->  Group  (cost=0.02..0.03 rows=1 width=40)
              ->  Sort  (cost=0.02..0.02 rows=1 width=40)
                    ->  Nested Loop  (cost=0.00..0.01 rows=1 width=40)
                          ->  Seq Scan on websensebycat a  (cost=0.00..0.00 rows=1 width=24)
                          ->  Seq Scan on netscapereduce b  (cost=0.00..0.00 rows=1 width=16)
                                SubPlan
                                  ->  Seq Scan on urlcounts  (cost=0.00..0.00 rows=1 width=12)

>
> --
> Joel Burton   <jburton@scw.org>
> Director of Information Systems, Support Center of Washington
>

--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres