Re: High memory usage

Ranier -
Can you explain in words what this query is supposed to be doing?

I'm guessing, from the DISTINCT, and the use of multiple occurances of
the same table, that the result you want can be gotten at in some other
way, that lets the backend be smarter about how it does it. Since it _is_
releasing the memory, i.e., it's not a new leak, I'm guessing that Tom
just got a whole lot less interested ;-) But helping you use PostgreSQL
better is part of what the community does, as well.

Hmm, you mention that _planning_ this query sucks up the memory, as well.
My guess is it's an interaction of the optimizer with the plan for this
query, which might have many, nearly identical cost plans, since 8 of
the 9 tables are actually the same table.

Ross

SELECT DISTINCT product.product_id
FROM     product,
    pr_prop_str alias_table_0,
    pr_prop_str alias_table_1,
    pr_prop_str alias_table_2,
    pr_prop_str alias_table_3,
    pr_prop_str alias_table_4,
    pr_prop_str alias_table_5,
    pr_prop_str alias_table_6,
    pr_prop_str alias_table_7,
    pr_prop_str alias_table_8
WHERE         product.product_id = alias_table_0.product_id
    AND product.product_id = alias_table_1.product_id
    AND product.product_id = alias_table_2.product_id
    AND product.product_id = alias_table_3.product_id
    AND product.product_id = alias_table_4.product_id
    AND product.product_id = alias_table_5.product_id
    AND product.product_id = alias_table_6.product_id
    AND product.product_id = alias_table_7.product_id
    AND product.product_id = alias_table_8.product_id
    AND ( alias_table_0.pr_property_id = 147
        AND alias_table_0.str = '3E362cb' )
    AND ( alias_table_1.pr_property_id = 18
        AND alias_table_1.str > '000999999' )
    AND ( alias_table_2.pr_property_id = 18
        AND alias_table_2.str < '004999999' )
    AND ( alias_table_3.pr_property_id = 51
        AND alias_table_3.str = '?$Bi_O?$B%&e_C~O?$Be_C?$B%&e_C~I?$Be_C?$B%)' )
    AND ( alias_table_4.pr_property_id = 115
        AND alias_table_4.str = '1' )
    AND ( alias_table_5.pr_property_id = 68
        AND alias_table_5.str = '05' )
    AND ( alias_table_6.pr_property_id = 113
        AND alias_table_6.str < '030001' )
    AND ( alias_table_7.pr_property_id = 57
        AND alias_table_7.str < '19980101' )
    AND ( alias_table_8.pr_property_id = 158
        AND alias_table_8.str = '1' );

On Wed, Jun 20, 2001 at 04:18:52PM +0900, Rainer Mager wrote:
> Hi,
>
>     Here is a query that demonstrates the problem. Running this takes about
> 60MB until it is done at which time it is freed (I was wrong when I said
> otherwise earlier). Interestingly, the same amount of memory is used when
> doing an EXPLAIN on this query. Also it happens to return 0 rows. Please
> excuse the weird characters in the middle this is a Japanese (UTF8)
> database. Also please excuse Outlook breaking the query, it is just one long
> line.
>
>