Skip site navigation (1) Skip section navigation (2)

RE: High memory usage

From: "Rainer Mager" <rmager(at)vgkk(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "PostgreSQL Admin" <pgsql-admin(at)postgresql(dot)org>
Subject: RE: High memory usage
Date: 2001-06-20 08:01:22
Message-ID: NEBBJBCAFMMNIHGDLFKGEEGAEFAA.rmager@vgkk.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-jdbcpgsql-patches
More info on this...

First, this type of query seems to use exponential amounts of memory. I
tried a join with 10 pieces (the one below with 9 used ~60MB) and it went up
immediately to ~ 390MB usage and has been sitting there at 100% CPU usage
for more than 10 minutes. This is on a database with almost no data in it.
Similarly, a query with only 8 joins is very quick and has much lower memory
usage.


Best regards,

--Rainer


> -----Original Message-----
> From: Rainer Mager [mailto:rmager(at)vgkk(dot)com]
> Sent: Wednesday, June 20, 2001 4:19 PM
> To: Tom Lane
> Cc: PostgreSQL Admin
> Subject: RE: [ADMIN] High memory usage
>
>
> 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.
>
>
> 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 = '蛬Oウ縷C~O縷Cウ縷C~I縷Cォ' ) 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' );


In response to

Responses

pgsql-admin by date

Next:From: Rustem F.UsupovDate: 2001-06-20 09:02:52
Subject: Get data from database files?
Previous:From: Ngoc-Khoi TODate: 2001-06-20 07:32:18
Subject: Re: interfacing ms-access with postgresql?

pgsql-patches by date

Next:From: Jason TishlerDate: 2001-06-20 13:39:15
Subject: Re: [PATCHES] Cygwin contrib patch
Previous:From: Rainer MagerDate: 2001-06-20 07:18:52
Subject: RE: High memory usage

pgsql-jdbc by date

Next:From: Gunnar RønningDate: 2001-06-20 11:03:38
Subject: Re: GUI for PostgreSQL
Previous:From: Rainer MagerDate: 2001-06-20 07:18:52
Subject: RE: High memory usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group