Обсуждение: Very long running query

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.

Konstantin
--
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

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?

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

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

On Wed, Apr 11, 2001 at 11:03:53AM +0200, Konstantinos Agouros wrote:

> > 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)

Umm, standard question:  Have you run VACUUM ANALYZE?

Chris

--
chris@mt.sri.com -----------------------------------------------------
Chris Jones                                    SRI International, Inc.
                                                           www.sri.com