pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
*very* slow query to summarize data for a month ...
19+ messages / 8 participants
[nested] [flat]

* *very* slow query to summarize data for a month ...
@ 2003-11-10 20:18 Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 20:31 ` Re: *very* slow query to summarize data for a month ... Patrick Hatcher <PHatcher@macys.com>
  2003-11-10 20:59 ` Re: *very* slow query to summarize data for a month ... Josh Berkus <josh@agliodbs.com>
  2003-11-10 23:15 ` Re: *very* slow query to summarize data for a month ... Neil Conway <neilc@samurai.com>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 4 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-10 20:18 UTC (permalink / raw)
  To: pgsql-performance


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.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;

and the explain looks like:
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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) (actual time=32957.40..42817.88 rows=462198 loops=1)
         ->  Sort  (cost=32000.94..32021.47 rows=8213 width=41) (actual time=32957.38..36261.31 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41) (actual time=13983.07..22642.14 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.52..7.40 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.02..2.78 rows=352 loops=1)
                     ->  Sort  (cost=31297.04..31317.57 rows=8213 width=16) (actual time=13977.49..16794.41 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  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)
 Total runtime: 47587.82 msec
(14 rows)

the problem is that we're only taking a few months worth of data, so I
don't think there is much of a way of 'improve performance' on this, but
figured I'd ask quickly before I do something rash ...

Note that without the month_trunc() index, the Total runtime more then
doubles:

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39578.63..39660.76 rows=821 width=41) (actual time=87805.47..101251.35 rows=144 loops=1)
   ->  Group  (cost=39578.63..39640.23 rows=8213 width=41) (actual time=87779.56..96824.56 rows=462198 loops=1)
         ->  Sort  (cost=39578.63..39599.17 rows=8213 width=41) (actual time=87779.52..90781.48 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=38899.14..39044.62 rows=8213 width=41) (actual time=64073.98..72783.68 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=64.66..66.55 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=1.76..61.70 rows=352 loops=1)
                     ->  Sort  (cost=38874.73..38895.27 rows=8213 width=16) (actual time=64009.26..66860.71 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 101277.17 msec
(14 rows)




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-10 20:31 ` Patrick Hatcher <PHatcher@macys.com>
  2003-11-10 22:51   ` Re: *very* slow query to summarize data for a month ... Neil Conway <neilc@samurai.com>
  3 siblings, 1 reply; 19+ messages in thread

From: Patrick Hatcher @ 2003-11-10 20:31 UTC (permalink / raw)
  To: scrappy@postgresql.org; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org


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 G. Fournier"                                              
           <scrappy@postgresql                                             
           .org>                                                        To 
           Sent by:                   pgsql-performance@postgresql.org     
           pgsql-performance-o                                          cc 
           wner@postgresql.org                                             
                                                                   Subject 
                                      [PERFORM] *very* slow query to       
           11/10/2003 12:18 PM        summarize data for a month ...       
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           





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.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;

and the explain looks like:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 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) (actual
time=32957.40..42817.88 rows=462198 loops=1)
         ->  Sort  (cost=32000.94..32021.47 rows=8213 width=41) (actual
time=32957.38..36261.31 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41)
(actual time=13983.07..22642.14 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25)
(actual time=5.52..7.40 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52
rows=352 width=25) (actual time=0.02..2.78 rows=352 loops=1)
                     ->  Sort  (cost=31297.04..31317.57 rows=8213 width=16)
(actual time=13977.49..16794.41 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  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)
 Total runtime: 47587.82 msec
(14 rows)

the problem is that we're only taking a few months worth of data, so I
don't think there is much of a way of 'improve performance' on this, but
figured I'd ask quickly before I do something rash ...

Note that without the month_trunc() index, the Total runtime more then
doubles:


QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=39578.63..39660.76 rows=821 width=41) (actual
time=87805.47..101251.35 rows=144 loops=1)
   ->  Group  (cost=39578.63..39640.23 rows=8213 width=41) (actual
time=87779.56..96824.56 rows=462198 loops=1)
         ->  Sort  (cost=39578.63..39599.17 rows=8213 width=41) (actual
time=87779.52..90781.48 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=38899.14..39044.62 rows=8213 width=41)
(actual time=64073.98..72783.68 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25)
(actual time=64.66..66.55 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52
rows=352 width=25) (actual time=1.76..61.70 rows=352 loops=1)
                     ->  Sort  (cost=38874.73..38895.27 rows=8213 width=16)
(actual time=64009.26..66860.71 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts
(cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04
rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text,
runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 101277.17 msec
(14 rows)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings






^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 20:31 ` Re: *very* slow query to summarize data for a month ... Patrick Hatcher <PHatcher@macys.com>
@ 2003-11-10 22:51   ` Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 19+ messages in thread

From: Neil Conway @ 2003-11-10 22:51 UTC (permalink / raw)
  To: Patrick Hatcher <PHatcher@macys.com>; +Cc: scrappy@postgresql.org; pgsql-performance

"Patrick Hatcher" <PHatcher@macys.com> 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




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-10 20:59 ` Josh Berkus <josh@agliodbs.com>
  2003-11-11 00:49   ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  3 siblings, 1 reply; 19+ messages in thread

From: Josh Berkus @ 2003-11-10 20:59 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance

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) (actual 
time=32957.40..42817.88 rows=462198 loops=1)

and:

>                ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41) 
(actual time=13983.07..22642.14 rows=462198 loops=1)
>                      Merge Cond: ("outer".company_id = "inner".company_id)
>                      ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual 
time=5.52..7.40 rows=348 loops=1)

There are also *large* delays between steps.    Either your I/O is saturated, 
or you haven't run a VACUUM FULL ANALYZE in a while (which would also explain 
the estimates being off).


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 20:59 ` Re: *very* slow query to summarize data for a month ... Josh Berkus <josh@agliodbs.com>
@ 2003-11-11 00:49   ` Marc G. Fournier <scrappy@postgresql.org>
  0 siblings, 0 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-11 00:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance



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, 304 sleeping, 1 zombie
CPU states: 18.0% user,  0.0% nice, 29.1% system,  0.6% interrupt, 52.3% idle
Mem: 1203M Active, 1839M Inact, 709M Wired, 206M Cache, 199M Buf, 5608K Free
Swap: 8192M Total, 1804K Used, 8190M Free

>
> >  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) (actual
> time=32957.40..42817.88 rows=462198 loops=1)
>
> and:
>
> >                ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41)
> (actual time=13983.07..22642.14 rows=462198 loops=1)
> >                      Merge Cond: ("outer".company_id = "inner".company_id)
> >                      ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual
> time=5.52..7.40 rows=348 loops=1)
>
> There are also *large* delays between steps.    Either your I/O is saturated,
> or you haven't run a VACUUM FULL ANALYZE in a while (which would also explain
> the estimates being off).

thought about that before I started the thread, and ran it just in case ...

just restarted the server with sort_mem set to 10M, and didn't help much on the Aggregate, or MergeJoin ... :

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39674.38..39756.70 rows=823 width=41) (actual time=33066.25..54021.50 rows=144 loops=1)
   ->  Group  (cost=39674.38..39736.12 rows=8232 width=41) (actual time=33040.25..47005.57 rows=462198 loops=1)
         ->  Sort  (cost=39674.38..39694.96 rows=8232 width=41) (actual time=33040.22..37875.97 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=38993.22..39139.02 rows=8232 width=41) (actual time=14428.17..23568.80 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.80..7.66 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.08..3.06 rows=352 loops=1)
                     ->  Sort  (cost=38968.82..38989.40 rows=8232 width=16) (actual time=14422.27..17429.34 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts  (cost=0.00..38433.46 rows=8232 width=16) (actual time=0.15..8119.72 rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 54034.44 msec
(14 rows)

the problem is that the results we are comparing with right now is the one
that had the - time on it :(  Just restarted the server with default
sort_mem, and here is the query with that:

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39691.27..39773.61 rows=823 width=41) (actual time=35077.18..50424.74 rows=144 loops=1)
   ->  Group  (cost=39691.27..39753.03 rows=8234 width=41) (actual time=35051.29..-650049.84 rows=462198 loops=1)
         ->  Sort  (cost=39691.27..39711.86 rows=8234 width=41) (actual time=35051.26..38847.40 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=39009.92..39155.76 rows=8234 width=41) (actual time=16155.37..25439.42 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.85..7.71 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.10..3.07 rows=352 loops=1)
                     ->  Sort  (cost=38985.51..39006.10 rows=8234 width=16) (actual time=16149.46..19437.47 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts  (cost=0.00..38450.00 rows=8234 width=16) (actual time=0.16..8869.37 rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 50426.80 msec
(14 rows)


And, just on a whim, here it is set to 100M:

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39691.27..39773.61 rows=823 width=41) (actual time=25888.20..38909.88 rows=144 loops=1)
   ->  Group  (cost=39691.27..39753.03 rows=8234 width=41) (actual time=25862.81..34591.76 rows=462198 loops=1)
         ->  Sort  (cost=39691.27..39711.86 rows=8234 width=41) (actual time=25862.77..723885.95 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=39009.92..39155.76 rows=8234 width=41) (actual time=12471.23..21855.08 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.87..7.74 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.11..3.14 rows=352 loops=1)
                     ->  Sort  (cost=38985.51..39006.10 rows=8234 width=16) (actual time=12465.29..14941.24 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts  (cost=0.00..38450.00 rows=8234 width=16) (actual time=0.18..9106.16 rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 39077.75 msec
(14 rows)

So, it does give a noticeable improvement the higher the sort_mem ...

And, @ 100M for sort_mem and using the month_trunc index:

                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=32089.29..32171.63 rows=823 width=41) (actual time=30822.51..57202.44 rows=144 loops=1)
   ->  Group  (cost=32089.29..32151.04 rows=8234 width=41) (actual time=30784.24..743396.18 rows=462198 loops=1)
         ->  Sort  (cost=32089.29..32109.87 rows=8234 width=41) (actual time=30784.21..36212.96 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=31407.94..31553.77 rows=8234 width=41) (actual time=11384.79..24918.56 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.92..9.55 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.08..3.21 rows=352 loops=1)
                     ->  Sort  (cost=31383.53..31404.12 rows=8234 width=16) (actual time=11378.81..15211.07 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Index Scan using tl_month on traffic_logs ts  (cost=0.00..30848.02 rows=8234 width=16) (actual time=0.46..7055.75 rows=462198 loops=1)
                                 Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 57401.72 msec
(14 rows)




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-10 23:15 ` Neil Conway <neilc@samurai.com>
  2003-11-10 23:42   ` Re: *very* slow query to summarize data for a month ... Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-11 00:19   ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  3 siblings, 2 replies; 19+ messages in thread

From: Neil Conway @ 2003-11-10 23:15 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

"Marc G. Fournier" <scrappy@postgresql.org> 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)

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, but I haven't looked into
it at all. Tom might be able to shed some light...

[ In the second EXPLAIN ANALYZE, ... ]

> ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)
>       Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)

Uh, what? The "actual time" seems to have finished far before it has
begun :-) Is this just a typo, or does the actual output include a
negative number?

-Neil




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 23:15 ` Re: *very* slow query to summarize data for a month ... Neil Conway <neilc@samurai.com>
@ 2003-11-10 23:42   ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-11 00:28     ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  1 sibling, 1 reply; 19+ messages in thread

From: Tom Lane @ 2003-11-10 23:42 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance

Neil Conway <neilc@samurai.com> 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, but I haven't looked into
> it at all. Tom might be able to shed some light...

Try "none at all".  I have speculated in the past that it would be worth
gathering statistics about the contents of functional indexes, but it's
still on the to-do-someday list.

>> ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)

> Uh, what?

That is bizarre, all right.  Is it reproducible?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 23:15 ` Re: *very* slow query to summarize data for a month ... Neil Conway <neilc@samurai.com>
  2003-11-10 23:42   ` Re: *very* slow query to summarize data for a month ... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-11 00:28     ` Marc G. Fournier <scrappy@postgresql.org>
  0 siblings, 0 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-11 00:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Neil Conway <neilc@samurai.com>; Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance



On Mon, 10 Nov 2003, Tom Lane wrote:

> Neil Conway <neilc@samurai.com> 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, but I haven't looked into
> > it at all. Tom might be able to shed some light...
>
> Try "none at all".  I have speculated in the past that it would be worth
> gathering statistics about the contents of functional indexes, but it's
> still on the to-do-someday list.
>
> >> ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)
>
> > Uh, what?
>
> That is bizarre, all right.  Is it reproducible?

Nope, and a subsequent run shows better results too:

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39674.38..39756.70 rows=823 width=41) (actual time=35573.27..49953.47 rows=144 loops=1)
   ->  Group  (cost=39674.38..39736.12 rows=8232 width=41) (actual time=35547.27..45479.27 rows=462198 loops=1)
         ->  Sort  (cost=39674.38..39694.96 rows=8232 width=41) (actual time=35547.23..39167.90 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=38993.22..39139.02 rows=8232 width=41) (actual time=16658.23..25559.08 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual time=5.51..7.38 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52 rows=352 width=25) (actual time=0.02..2.80 rows=352 loops=1)
                     ->  Sort  (cost=38968.82..38989.40 rows=8232 width=16) (actual time=16652.66..19785.83 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts  (cost=0.00..38433.46 rows=8232 width=16) (actual time=0.11..8794.43 rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 49955.22 msec




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-10 23:15 ` Re: *very* slow query to summarize data for a month ... Neil Conway <neilc@samurai.com>
@ 2003-11-11 00:19   ` Marc G. Fournier <scrappy@postgresql.org>
  1 sibling, 0 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-11 00:19 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>



On Mon, 10 Nov 2003, Neil Conway wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> 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)
>
> 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, but I haven't looked into
> it at all. Tom might be able to shed some light...
>
> [ In the second EXPLAIN ANALYZE, ... ]
>
> > ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)
> >       Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
>
> Uh, what? The "actual time" seems to have finished far before it has
> begun :-) Is this just a typo, or does the actual output include a
> negative number?

This was purely a cut-n-paste ...




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-11 06:50 ` Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 17:47   ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  2003-11-11 18:38   ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  3 siblings, 2 replies; 19+ messages in thread

From: Dennis Bjorklund @ 2003-11-11 06:50 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-performance

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;

What if you do

  ts.runtime >= '2003-10-01' AND ts.runtime < '2003-11-01'

and add an index like (runtime, company_name, company_id)?


-- 
/Dennis




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
@ 2003-11-11 17:47   ` Greg Stark <gsstark@mit.edu>
  2003-11-11 18:14     ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  1 sibling, 1 reply; 19+ messages in thread

From: Greg Stark @ 2003-11-11 17:47 UTC (permalink / raw)
  To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance


Dennis Bjorklund <db@zigo.dhs.org> 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.runtime) = '2003-10-01'
> > GROUP BY company_name,ts.company_id;

So depending on how much work you're willing to do there are some more
dramatic speedups you could get:

Use partial indexes like this (you'll need one for every month):

create index i on traffic_log (company_id) 
 where month_trunc(runtime) = '2003-10-01'

then group by company_id only so it can use the index:

select * 
  from company
  join (
        select company_id, sum(bytes) as total_traffic
          from traffic_log
         where month_trunc(runtime) = '2003-10-01'
         group by company_id
       ) as x using (company_id)
  order by company_name



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)


-- 
greg




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 17:47   ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
@ 2003-11-11 18:14     ` Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 18:30       ` Re: *very* slow query to summarize data for a month ... Josh Berkus <josh@agliodbs.com>
  2003-11-11 19:51       ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-11 18:14 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance


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 i on traffic_logs ( month_trunc(runtime), company_id );
ERROR:  parser: parse error at or near "," at character 54




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 17:47   ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  2003-11-11 18:14     ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-11 18:30       ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 19+ messages in thread

From: Josh Berkus @ 2003-11-11 18:30 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; Greg Stark <gsstark@mit.edu>; +Cc: Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 17:47   ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  2003-11-11 18:14     ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-11 19:51       ` Greg Stark <gsstark@mit.edu>
  2003-11-11 21:25         ` Re: *very* slow query to summarize data for a month ... scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 1 reply; 19+ messages in thread

From: Greg Stark @ 2003-11-11 19:51 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: Greg Stark <gsstark@mit.edu>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

"Marc G. Fournier" <scrappy@postgresql.org> 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 something that is only
> available in v7.4?

Hum, I thought you could do simple functional indexes like that in 7.3, but
perhaps only single-column indexes.

In any case, given your situation I would seriously consider putting a
"month" integer column on your table anyways. Then your index would be a
simple (month, company_id) index.

-- 
greg




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 17:47   ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  2003-11-11 18:14     ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 19:51       ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
@ 2003-11-11 21:25         ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 19+ messages in thread

From: scott.marlowe @ 2003-11-11 21:25 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

On 11 Nov 2003, Greg Stark wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> 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 something that is only
> > available in v7.4?
> 
> Hum, I thought you could do simple functional indexes like that in 7.3, but
> perhaps only single-column indexes.
> 
> In any case, given your situation I would seriously consider putting a
> "month" integer column on your table anyways. Then your index would be a
> simple (month, company_id) index.

In 7.3 and before, you had to use only column names as inputs, so you 
could cheat:

alter table test add alp int;
alter table test add omg int;
update test set alp=0;
update test set omg=13;

and then create a functional index:

create index test_xy on test (substr(info,alp,omg));

select * from test where substr(info,alp,omg)=='abcd';







^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
@ 2003-11-11 18:38   ` Marc G. Fournier <scrappy@postgresql.org>
  2003-11-12 05:52     ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
  1 sibling, 1 reply; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-11 18:38 UTC (permalink / raw)
  To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-performance



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.runtime) = '2003-10-01'
> > GROUP BY company_name,ts.company_id;
>
> What if you do
>
>   ts.runtime >= '2003-10-01' AND ts.runtime < '2003-11-01'
>
> and add an index like (runtime, company_name, company_id)?

Good thought, but even simplifying it to the *lowest* query possible, with
no table joins, is painfully slow:

explain analyze SELECT ts.company_id, SUM(ts.bytes) AS total_traffic
    FROM traffic_logs ts
   WHERE month_trunc(ts.runtime) = '2003-10-01'
GROUP BY ts.company_id;


                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=31630.84..31693.05 rows=829 width=16) (actual time=14862.71..26552.39 rows=144 loops=1)
   ->  Group  (cost=31630.84..31672.31 rows=8295 width=16) (actual time=9634.28..20967.07 rows=462198 loops=1)
         ->  Sort  (cost=31630.84..31651.57 rows=8295 width=16) (actual time=9634.24..12838.73 rows=462198 loops=1)
               Sort Key: company_id
               ->  Index Scan using tl_month on traffic_logs ts  (cost=0.00..31090.93 rows=8295 width=16) (actual time=0.26..6043.35 rows=462198 loops=1)
                     Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 26659.35 msec
(7 rows)



-OR-

explain analyze SELECT ts.company_id, SUM(ts.bytes) AS total_traffic
    FROM traffic_logs ts
   WHERE ts.runtime >= '2003-10-01' AND ts.runtime < '2003-11-01'
GROUP BY ts.company_id;


                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=81044.53..84424.21 rows=45062 width=16) (actual time=13307.52..29274.66 rows=144 loops=1)
   ->  Group  (cost=81044.53..83297.65 rows=450625 width=16) (actual time=10809.02..-673265.13 rows=462198 loops=1)
         ->  Sort  (cost=81044.53..82171.09 rows=450625 width=16) (actual time=10808.99..14069.79 rows=462198 loops=1)
               Sort Key: company_id
               ->  Seq Scan on traffic_logs ts  (cost=0.00..38727.35 rows=450625 width=16) (actual time=0.07..6801.92 rows=462198 loops=1)
                     Filter: ((runtime >= '2003-10-01 00:00:00'::timestamp without time zone) AND (runtime < '2003-11-01 00:00:00'::timestamp without time zone))
 Total runtime: 29385.97 msec
(7 rows)


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:

                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tl_month on traffic_logs ts  (cost=0.00..31096.36 rows=8297 width=16) (actual time=0.96..5432.93 rows=462198 loops=1)
   Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 8092.88 msec
(3 rows)

and without the index, >15k msec:

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on traffic_logs ts  (cost=0.00..38719.55 rows=8297 width=16) (actual time=0.11..11354.45 rows=462198 loops=1)
   Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 15353.57 msec
(3 rows)

so the GROUP BY is affecting the overall, but even without it, its still
taking a helluva long time ...

I'm going to modify my load script so that it dumps monthly totals to
traffic_logs, and 'details' to a schema.traffic_logs table ... I don't
need the 'per day totals' at the top level at all, only speed ... the 'per
day totals' are only required at the 'per client' level, and by moving the
'per day' into a client schema will shrink the table significantly ...

If it wasn't for trying to pull in that 'whole month' summary, it would be
fine :(



^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 18:38   ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-11-12 05:52     ` Greg Stark <gsstark@mit.edu>
  2003-11-12 16:08       ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  0 siblings, 1 reply; 19+ messages in thread

From: Greg Stark @ 2003-11-12 05:52 UTC (permalink / raw)
  To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance


"Marc G. Fournier" <scrappy@postgresql.org> 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 preprocess the data offline into a more managable
amount of data for your online reports.

What I used to do for a similar situation was to do hourly queries sort of
like this:

insert into data_aggregate (day, hour, company_id, total_bytes)
 (select trunc(now(),'day'), trunc(now(), 'hour'), company_id, sum(bytes)
    from raw_data
   where time between trunc(now(),'hour') and trunc(now(),'hour')+'1 hour'::interval
   group by company_id
 )

[this was actually on oracle and the data looked kind of different, i'm making
this up as i go along]

Then later the reports could run quickly based on data_aggregate instead of
slowly based on the much larger data set accumulated by the minute. Once I had
this schema set up it was easy to follow it for all of the rapidly growing
data tables.

Now in my situation I had thousands of records accumulating per second, so
hourly was already a big win. I originally chose hourly because I thought I
might want time-of-day reports but that never panned out. On the other hand it
was a win when the system broke once because I could easily see that and fix
it before midnight when it would have actually mattered. Perhaps in your
situation you would want daily aggregates or something else.

One of the other advantages of these aggregate tables was that we could purge
the old data much sooner with much less resistance from the business. Since
the reports were all still available and a lot of ad-hoc queries could still
be done without the raw data anyways.

Alternatively you can just give up on online reports. Eventually you'll have
some query that takes way more than 8s anyways. You can pregenerate the entire
report as a batch job instead. Either send it off as a nightly e-mail, store
it as an html or csv file for the web server, or (my favourite) store the data
for the report as an sql table and then have multiple front-ends that do a
simple "select *" to pull the data and format it.

-- 
greg




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
  2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-11 06:50 ` Re: *very* slow query to summarize data for a month ... Dennis Bjorklund <db@zigo.dhs.org>
  2003-11-11 18:38   ` Re: *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
  2003-11-12 05:52     ` Re: *very* slow query to summarize data for a month ... Greg Stark <gsstark@mit.edu>
@ 2003-11-12 16:08       ` Marc G. Fournier <scrappy@postgresql.org>
  0 siblings, 0 replies; 19+ messages in thread

From: Marc G. Fournier @ 2003-11-12 16:08 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance



On Wed, 12 Nov 2003, Greg Stark wrote:

>
> "Marc G. Fournier" <scrappy@postgresql.org> 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 tables was that we could
> purge the old data much sooner with much less resistance from the
> business. Since the reports were all still available and a lot of ad-hoc
> queries could still be done without the raw data anyways.

Actually, what I've done is do this at the 'load stage' ... but same
concept ...




^ permalink  raw  reply  [nested|flat] 19+ messages in thread

* Re: *very* slow query to summarize data for a month ...
@ 2003-11-10 20:32 Patrick Hatcher <PHatcher@macys.com>
  0 siblings, 0 replies; 19+ messages in thread

From: Patrick Hatcher @ 2003-11-10 20:32 UTC (permalink / raw)
  To: pgsql-performance@postgresql.org, pgsql-performance-owner@postgresql.org, "Marc G. Fournier" <scrappy@postgresql.org>


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                                                         
           Hatcher/MCOM/FDD                                                
                                                                        To 
           11/10/2003 12:31 PM        "Marc G. Fournier"                   
                                      <scrappy@postgresql.org>@FDS-NOTES   
                                                                        cc 
                                      pgsql-performance@postgresql.org,    
                                      pgsql-performance-owner@postgresql.o 
                                      rg                                   
                                                                   Subject 
                                      Re: [PERFORM] *very* slow query to   
                                      summarize data for a month ...       
                                      (Document link: Patrick Hatcher)     
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           



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 G. Fournier"                                              
           <scrappy@postgresql                                             
           .org>                                                        To 
           Sent by:                   pgsql-performance@postgresql.org     
           pgsql-performance-o                                          cc 
           wner@postgresql.org                                             
                                                                   Subject 
                                      [PERFORM] *very* slow query to       
           11/10/2003 12:18 PM        summarize data for a month ...       
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           





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.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;

and the explain looks like:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 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) (actual
time=32957.40..42817.88 rows=462198 loops=1)
         ->  Sort  (cost=32000.94..32021.47 rows=8213 width=41) (actual
time=32957.38..36261.31 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41)
(actual time=13983.07..22642.14 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25)
(actual time=5.52..7.40 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52
rows=352 width=25) (actual time=0.02..2.78 rows=352 loops=1)
                     ->  Sort  (cost=31297.04..31317.57 rows=8213 width=16)
(actual time=13977.49..16794.41 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  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)
 Total runtime: 47587.82 msec
(14 rows)

the problem is that we're only taking a few months worth of data, so I
don't think there is much of a way of 'improve performance' on this, but
figured I'd ask quickly before I do something rash ...

Note that without the month_trunc() index, the Total runtime more then
doubles:


QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=39578.63..39660.76 rows=821 width=41) (actual
time=87805.47..101251.35 rows=144 loops=1)
   ->  Group  (cost=39578.63..39640.23 rows=8213 width=41) (actual
time=87779.56..96824.56 rows=462198 loops=1)
         ->  Sort  (cost=39578.63..39599.17 rows=8213 width=41) (actual
time=87779.52..90781.48 rows=462198 loops=1)
               Sort Key: c.company_name, ts.company_id
               ->  Merge Join  (cost=38899.14..39044.62 rows=8213 width=41)
(actual time=64073.98..72783.68 rows=462198 loops=1)
                     Merge Cond: ("outer".company_id = "inner".company_id)
                     ->  Sort  (cost=24.41..25.29 rows=352 width=25)
(actual time=64.66..66.55 rows=348 loops=1)
                           Sort Key: c.company_id
                           ->  Seq Scan on company c  (cost=0.00..9.52
rows=352 width=25) (actual time=1.76..61.70 rows=352 loops=1)
                     ->  Sort  (cost=38874.73..38895.27 rows=8213 width=16)
(actual time=64009.26..66860.71 rows=462198 loops=1)
                           Sort Key: ts.company_id
                           ->  Seq Scan on traffic_logs ts
(cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04
rows=462198 loops=1)
                                 Filter: (date_trunc('month'::text,
runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
 Total runtime: 101277.17 msec
(14 rows)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings







^ permalink  raw  reply  [nested|flat] 19+ messages in thread


end of thread, other threads:[~2003-11-12 16:08 UTC | newest]

Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-10 20:18 *very* slow query to summarize data for a month ... Marc G. Fournier <scrappy@postgresql.org>
2003-11-10 20:31 ` Patrick Hatcher <PHatcher@macys.com>
2003-11-10 22:51   ` Neil Conway <neilc@samurai.com>
2003-11-10 20:59 ` Josh Berkus <josh@agliodbs.com>
2003-11-11 00:49   ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-10 23:15 ` Neil Conway <neilc@samurai.com>
2003-11-10 23:42   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-11 00:28     ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-11 00:19   ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-11 06:50 ` Dennis Bjorklund <db@zigo.dhs.org>
2003-11-11 17:47   ` Greg Stark <gsstark@mit.edu>
2003-11-11 18:14     ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-11 18:30       ` Josh Berkus <josh@agliodbs.com>
2003-11-11 19:51       ` Greg Stark <gsstark@mit.edu>
2003-11-11 21:25         ` scott.marlowe <scott.marlowe@ihs.com>
2003-11-11 18:38   ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-12 05:52     ` Greg Stark <gsstark@mit.edu>
2003-11-12 16:08       ` Marc G. Fournier <scrappy@postgresql.org>
2003-11-10 20:32 Re: *very* slow query to summarize data for a month ... Patrick Hatcher <PHatcher@macys.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox