Re: Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 13:11, Tom Lane wrote:
> Toby Corkindale<toby.corkindale@strategicdata.com.au>  writes:
>> Just wondering if anyone else has thoughts on this?
>
>> I'm still suspicious that this is a bug.
>
> Well, if you were to provide a reproducible test case, somebody might be
> motivated to look into it.  There could be a memory leak in the planner
> somewhere, but without a test case it's not very practical to go look
> for it.

Hi Tom,
Thanks for responding.. I'm trying to work on a test case, but it's
quite tricky.
It'll need to be something like a script that generates a tonne of
partitions at the very least. I don't know if the actual amount of data
in the partitions is part of the problem or not.
Would a Perl-based script that built up a database like that be a useful
test case for you?


For what it's worth, I discovered something quite interesting. The
memory usage only blows out when I do an update based on the results of
the query. But not if I just select the results on their own, nor if I
do the update using those values on its own.

ie.

Method #1, uses all the memory and doesn't return it:
   explain update line set status = 'foo'
   where file_id=725 and line.lineno in (
     select line from complex_view
     where file_id=725
   );


Method #2, also uses all the memory:
   explain update line set status = 'foo'
   from complex_view v
   where line.lineno = v.line
   and line.file_id=725
   and v.file_id=725;


Method #3, which uses next to no memory:
   explain select line from complex_view
   where file_id=725;


Method #4, which also uses next to no memory:
   explain create temp table foo as
   select line from complex_view;

   where file_id=725;
   update line set status = 'foo'
   from foo
   where line.lineno=foo.line
     and file_id=725;


-Toby