Thanks a lot. IT WORKED! with your suggestions.
Regards,
Radha
> On Sun, 2003-11-09 at 15:06, [EMAIL PROTECTED] wrote:
>> I have a c program called test1.pgc with some sql statements embedded
>> in it. The program was preprocessed, compiled and linked. Now, I have
>> the executable test1.
>>
>> W
Table structure is simple:
CREATE TABLE traffic_logs (
company_id bigint,
ip_id bigint,
port integer,
bytes bigint,
runtime timestamp without time zone
);
runtime is 'day of month' ...
I need to summarize the month, per company, with a query as:
explain analyze SELECT ts.co
here's the URL:
http://techdocs.postgresql.org/techdocs/pgsqladventuresep2.php
Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM
Patrick
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
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)
"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 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
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,
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
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, 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, 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
12 matches
Mail list logo
