Postgres Pro
Downloads
Home   >   mailing lists

Thread: Very long running query

Very long running query

From
elwood@agouros.de (Konstantinos Agouros)
Date:
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

Re: Very long running query

From
Joel Burton
Date:
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

Re: Very long running query

From
Konstantinos Agouros
Date:
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

Re: Re: Very long running query

From
Chris Jones
Date:
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
Attachment