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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026