On Wed, 12 Nov 2003, Greg Stark wrote:
>
> "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
>
> > Just as a side note, just doing a straight scan for the records, with no
> > SUM()/GROUP BY involved, with the month_trunc() index is still >8k msec:
>
> One of the other advantages of these aggregate
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Just as a side note, just doing a straight scan for the records, with no
> SUM()/GROUP BY involved, with the month_trunc() index is still >8k msec:
Well so the problem isn't the query at all, you just have too much data to
massage online. You can
On 11 Nov 2003, Greg Stark wrote:
> "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
>
> > On Tue, 11 Nov 2003, Greg Stark wrote:
> >
> > > Actually you might be able to get the same effect using function indexes
> > > like:
> > >
> > > create index i on traffic_log (month_trunc(runtime), company_
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> On Tue, 11 Nov 2003, Greg Stark wrote:
>
> > Actually you might be able to get the same effect using function indexes
> > like:
> >
> > create index i on traffic_log (month_trunc(runtime), company_id)
>
> had actually thought of that one ... is it
On Tue, 11 Nov 2003, Dennis Bjorklund wrote:
> On Mon, 10 Nov 2003, Marc G. Fournier wrote:
>
> >
> > explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic
> > FROM company c, traffic_logs ts
> >WHERE c.company_id = ts.company_id
> > AND month_trunc(ts.r
marc,
> had actually thought of that one ... is it something that is only
> available in v7.4?
Yes. New feature.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Tue, 11 Nov 2003, Greg Stark wrote:
> Actually you might be able to get the same effect using function indexes
> like:
>
> create index i on traffic_log (month_trunc(runtime), company_id)
had actually thought of that one ... is it something that is only
available in v7.4?
ams=# create index
Dennis Bjorklund <[EMAIL PROTECTED]> writes:
> On Mon, 10 Nov 2003, Marc G. Fournier wrote:
>
> >
> > explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic
> > FROM company c, traffic_logs ts
> >WHERE c.company_id = ts.company_id
> > AND month_trunc(ts.
On Mon, 10 Nov 2003, Marc G. Fournier wrote:
>
> explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic
> FROM company c, traffic_logs ts
>WHERE c.company_id = ts.company_id
> AND month_trunc(ts.runtime) = '2003-10-01'
> GROUP BY company_name,ts.company_id
On Mon, 10 Nov 2003, Josh Berkus wrote:
> Marc,
>
> I'd say your machine is very low on available RAM, particularly sort_mem.
> The steps which are taking a long time are:
Here's the server:
last pid: 42651; load averages: 1.52, 0.96, 0.88
up 28+07:43:33 20:35:44
307 processes: 2 running,
On Mon, 10 Nov 2003, Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > Interesting that we get the row count estimate for this index scan so
> > wrong -- I believe this is the root of the problem. Hmmm... I would
> > guess that the optimizer stats we have for estimating the selectivi
On Mon, 10 Nov 2003, Neil Conway wrote:
> "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> > -> Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213
> > width=16) (actual time=0.29..5562.25 rows=462198 loops=1)
> > Index Cond: (month_trunc(runtime) = '2003-10-01 0
Neil Conway <[EMAIL PROTECTED]> writes:
> Interesting that we get the row count estimate for this index scan so
> wrong -- I believe this is the root of the problem. Hmmm... I would
> guess that the optimizer stats we have for estimating the selectivity
> of a functional index is pretty primitive,
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> -> Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213
> width=16) (actual time=0.29..5562.25 rows=462198 loops=1)
> Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without
> time zone)
Interest
"Patrick Hatcher" <[EMAIL PROTECTED]> writes:
> Do you have an index on ts.bytes? Josh had suggested this and after I put
> it on my summed fields, I saw a speed increase.
What's the reasoning behind this? ISTM that sum() should never use an
index, nor would it benefit from using one.
-Neil
--
Marc,
I'd say your machine is very low on available RAM, particularly sort_mem.
The steps which are taking a long time are:
> Aggregate (cost=32000.94..32083.07 rows=821 width=41) (actual
time=32983.36..47586.17 rows=144 loops=1)
>-> Group (cost=32000.94..32062.54 rows=8213 width=41)
Do you have an index on ts.bytes? Josh had suggested this and after I put
it on my summed fields, I saw a speed increase. I can't remember the
article was that Josh had written about index usage, but maybe he'll chime
in and supply the URL for his article.
hth
Patrick Hatcher
rg
Subject
Re: [PERFORM] *very* slow query to
summarize data for a month ...
(Document
18 matches
Mail list logo
