Thread

1. out of memory with large queries

   Massimo Dal Zotto <dz@cs.unitn.it> — 1999-06-09T20:36:00Z

   Hi,
   
   I have a problem with large queries: I have a table with 300000 rows and
   when I try the following query the backends runs out of memory:
   
     select upper(name) from my_table;
   
   The following queries without funcs or with funcs of int4 work fine:
   
     select name from my_table;
     select max(id,0) from my_table;
   
   so I suspect that the trouble is with memory allocated by functions
   returning data by address, which is not released until the end of the
   transaction. With more then 300000 rows you eat a lot of memory.
   
   This means that postgres is currently unable to execute large queries
   that involve functions on text fields. A pretty bad limitation IMHO.
   
   I tried to look at the code but haven't found a way to release the
   storage allocated for each tuple and the context allocation code is
   not very documented.
   
   Any suggestion?
   
   -- 
   Massimo Dal Zotto
   
   +----------------------------------------------------------------------+
   |  Massimo Dal Zotto               email: dz@cs.unitn.it               |
   |  Via Marconi, 141                phone: ++39-0461534251              |
   |  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
   |  Italy                             pgp: finger dz@tango.cs.unitn.it  |
   +----------------------------------------------------------------------+
   
   

2. Re: [HACKERS] out of memory with large queries

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-06-09T21:14:12Z

   Massimo Dal Zotto <dz@cs.unitn.it> writes:
   > This means that postgres is currently unable to execute large queries
   > that involve functions on text fields. A pretty bad limitation IMHO.
   
   Yup.  This is one of the major projects that I was proposing for 6.6.
   I do not think the fix will be easy, but we need to do it.
   
   			regards, tom lane
   
   

3. Re: [HACKERS] out of memory with large queries

   Philip Warner <pjw@rhyme.com.au> — 1999-06-10T02:38:48Z

   At 17:14 9/06/99 -0400, you wrote:
   >Massimo Dal Zotto <dz@cs.unitn.it> writes:
   >> This means that postgres is currently unable to execute large queries
   >> that involve functions on text fields. A pretty bad limitation IMHO.
   >
   >Yup.  This is one of the major projects that I was proposing for 6.6.
   >I do not think the fix will be easy, but we need to do it.
   >
   
   In yet another attempt to go over my head, wouldn't it be possible to pass three parameters to the aggregate state functions, so their signatures become:
   
   int (or void) = transfn1 (transtype1, basetype, *transtype1)
   int (or void) = transfn2 (transtype2, *transtype2)
   int (or void) = finalfn (transtype1, transtype2, *finaltype)
   
   where the int return value *may* give status information, or may just be ignored.
   
   This clearly breaks the ability to use the existing float8pl etc functions, but the cost is reasonably controlled: the creation of xxxxx3 wrappers (eg. float8pl3) which take three parameters and call the original function, then release the memory.
   
   A better solution would be to have float8pl accept 2 or 3 parameters, and modify how it returns information based on the parameter count, but I *presume* that this would break on many platforms/compilers (is this right?), in any case it seems pretty nasty returning an int OR a float8*, depending on your parameters. Perhaps if the functions were modified as:
   
   transtype1 = transfn1 (transtype1, basetype [, *transtype1])
   transtype2 = transfn2 (transtype2 [, *transtype2])
   finaltype = finalfn (transtype1, transtype2 [, *finaltype])
   
   Then they are completely source-compatible with existing functions, but will accept an optional pointer to the storage for their return value (probably a local variable in the caller).
   
   If it worked, then each of the aggregates could avoid memory allocation completely, which has to be a performance gain as well. Unfortunately, even if this worked on all platforms, I have no idea what it would do to the internals of PG.
   
   Am I missing something (again?).
   
   
   ----------------------------------------------------------------
   Philip Warner                    |     __---_____
   Albatross Consulting Pty. Ltd.   |----/       -  \
   (A.C.N. 008 659 498)             |          /(@)   ______---_
   Tel: +61-03-5367 7422            |                 _________  \
   Fax: +61-03-5367 7430            |                 ___________ |
   Http://www.rhyme.com.au          |                /           \|
                                    |    --________--
   PGP key available upon request,  |  /
   and from pgp5.ai.mit.edu:11371   |/