Skip site navigation (1) Skip section navigation (2)

Re: Avg performance for int8/numeric

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>, "eng(at)intranet(dot)greenplum(dot)com" <eng(at)intranet(dot)greenplum(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Avg performance for int8/numeric
Date: 2006-11-25 00:46:22
Message-ID: 4567925E.7010803@paradise.net.nz (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Neil Conway wrote:

> 
>> (it is still slower than doing sum/count - possibly due to the
>> construct/deconstruct overhead of the numeric transition array).
> 
> This would indeed be worth profiling. If it turns out that array
> overhead is significant, I wonder if we could use a composite type for
> the transition variable instead. That might also make it easier to
> represent the "N" value as an int8 rather than a numeric.
> 

I've profiled the 2nd patch using the setup indicated below. The first 
64 lines of the flat graph are attached. The complete profile is here:

http://homepages.paradise.net.nz/markir/download/postgres/postgres-avg.gprof.gz

Setup:

avg=# \d avgtest
        Table "public.avgtest"
  Column |     Type      | Modifiers
--------+---------------+-----------
  id     | integer       |
  val0   | bigint        |
  val1   | numeric(12,2) |
  val2   | numeric(10,0) |

avg=# analyze verbose avgtest;
INFO:  analyzing "public.avgtest"
INFO:  "avgtest": scanned 3000 of 87689 pages, containing 342138 live 
rows and 0 dead rows; 3000 rows in sample, 10000580 estimated total rows
ANALYZE
Time: 252.033 ms
avg=# select avg(val2) from avgtest;
          avg
---------------------
  714285.214285800000
(1 row)

Time: 35196.028 ms
avg=# \q

regards

Mark

Attachment: postgres-avg.gprof.head
Description: text/plain (4.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Mark KirkwoodDate: 2006-11-25 00:48:39
Subject: Re: [PATCHES] Avg performance for int8/numeric
Previous:From: Mark KirkwoodDate: 2006-11-25 00:28:28
Subject: Re: Avg performance for int8/numeric

pgsql-patches by date

Next:From: Mark KirkwoodDate: 2006-11-25 00:48:39
Subject: Re: [PATCHES] Avg performance for int8/numeric
Previous:From: Mark KirkwoodDate: 2006-11-25 00:28:28
Subject: Re: Avg performance for int8/numeric

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group