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 websensebycat a, 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(at)scw(dot)org>
> Director of Information Systems, Support Center of Washington
>

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