Re: [HACKERS] aggregation memory leak and fix

> 
> Platform:  Alpha, Digital UNIX 4.0D
> Software:  PostgreSQL 6.4.2 and 6.5 snaphot (11 March 1999)
> 
> I have a table as follows:
> 
> Table    = lineitem
> +------------------------+----------------------------------+-------+
> |              Field     |              Type                | Length|
> +------------------------+----------------------------------+-------+
> | l_orderkey             | int4 not null                    |     4 |
> | l_partkey              | int4 not null                    |     4 |
> | l_suppkey              | int4 not null                    |     4 |
> | l_linenumber           | int4 not null                    |     4 |
> | l_quantity             | float4 not null                  |     4 |
> | l_extendedprice        | float4 not null                  |     4 |
> | l_discount             | float4 not null                  |     4 |
> | l_tax                  | float4 not null                  |     4 |
> | l_returnflag           | char() not null                  |     1 |
> | l_linestatus           | char() not null                  |     1 |
> | l_shipdate             | date                             |     4 |
> | l_commitdate           | date                             |     4 |
> | l_receiptdate          | date                             |     4 |
> | l_shipinstruct         | char() not null                  |    25 |
> | l_shipmode             | char() not null                  |    10 |
> | l_comment              | char() not null                  |    44 |
> +------------------------+----------------------------------+-------+
> Index:    lineitem_index_
> 
> that ends up having on the order of 500,000 rows (about 100 MB on disk).  
> 
> I then run an aggregation query as:
> 
> --
> -- Query 1
> --
> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, 
> sum(l_extendedprice) as sum_base_price, 
> sum(l_extendedprice*(1-l_discount)) as sum_disc_price, 
> sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, 
> avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, 
> avg(l_discount) as avg_disc, count(*) as count_order 
> from lineitem 
> where l_shipdate <= ('1998-12-01'::datetime - interval '90 day')::date 
> group by l_returnflag, l_linestatus 
> order by l_returnflag, l_linestatus;
> 

OK, I do have the query.  Please try removing the (1+l_tax) so it is
just l_tax, and change the 1998... to just a simple date string, and see
if the problem goes away.  If we can find something specific in the
query that is causing the memory over-allocation, it is that much easier
to find the cause.

Also, try removing all the arithmetic in the query or simplify the query
to see if there is a certain part that is causing it.  If it is really
an 8-byte issue, it must be very small indeed, and only visible because
you have so much data, and are attentive.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026