Обсуждение: Bug #715: Too much memory consuming with postmaster

Milan Hromada (mhromada@elas.sk) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Too much memory consuming with postmaster

Long Description
Big diffrence in performence between 7.1 and 7.2.1. In 7.2.1 postmaster grows up to 700 MB RAM and more while in 7.1
justup to 8 MB RAM.
 
7.1 has better response on 1.4 Ghz Athlon 256MB RAM then 7.2.1 on Dual 1GHz Intel Pentium III with 1GB RAM. Postmaster
cousumesall resoureces 

Sample Code
CREATE VIEW "faksluexport" AS SELECT "zakazkaoprava"."id_zakazka" as idzakazka,
"cisopravaceny"."cena_bezdph" as p_cena,
"cisopravaceny"."cena" as p_cena_c,"cisopravaceny"."kdph" as kdph,
"zakazka"."id_cispobocka" as pobocka, "cispobocka"."stredisko" as stredisko,"cisoprava"."popis" as
+snaz,
"cisdealer"."snaz" as snazo,"cisfakt"."ico" as ico,"cisfakt"."cislo_uctu" as cbu,"zakazka"."cislo" as
+zakazka,"cisbanka"."kod" as skb,"cisvyrobca"."znacka" as znacka,
"zakazka"."id_ciszaruka" as zaruka,
"zakazkauct"."storno" as storno
FROM "cisoprava","zakazkaoprava","cisopravaceny","cisdealer", "zakazka", "cisfakt",
+"zakazkauct","cispobocka","cisbanka","cisvyrobca","cismtop","cismttyp"
WHERE "zakazkaoprava"."id_cisoprava"="cisoprava"."id_cisoprava" AND
"cisoprava"."id_cisoprava"="cismtop"."id_cisoprava"
AND "cismtop"."id_cismtop"="cisopravaceny"."id_cismtop"
AND "cisfakt"."id_cisbanka"="cisbanka"."id_cisbanka"
AND "zakazka"."id_zakazka"="zakazkaoprava"."id_zakazka"
AND "cisopravaceny"."platnost_od"<="zakazka"."d_prijem"
AND ("cisopravaceny"."platnost_do">"zakazka"."d_prijem" OR "cisopravaceny"."platnost_do" IS NULL)
AND "cismtop"."id_cismttyp"="zakazka"."id_cismttyp"
AND "cismtop"."id_cismttyp"="cismttyp"."id_cismttyp"
AND "cisvyrobca"."id_cisvyrobca"="cismttyp"."id_cisvyrobca"
AND "zakazka"."id_zakazka"="zakazkauct"."id_zakazka" AND
+"zakazka"."id_cisdealer"="cisdealer"."id_cisdealer"
AND "cisfakt"."id_cisfakt"="cisdealer"."id_cisfakt" AND "zakazkauct"."d_export"='1'
AND "zakazkauct"."typ_platby"='0'
AND "zakazka"."id_cispobocka"="cispobocka"."id_cispobocka"
ORDER BY idzakazka,p_cena DESC;

No file was uploaded with this report

On Fri, 19 Jul 2002 pgsql-bugs@postgresql.org wrote:

> Milan Hromada (mhromada@elas.sk) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> Too much memory consuming with postmaster
>
> Long Description

> Big diffrence in performence between 7.1 and 7.2.1. In 7.2.1
> postmaster grows up to 700 MB RAM and more while in 7.1 just up to 8
> MB RAM. 7.1 has better response on 1.4 Ghz Athlon 256MB RAM then 7.2.1
> on Dual 1GHz Intel Pentium III with 1GB RAM. Postmaster cousumes all
> resoureces

>
> Sample Code
> CREATE VIEW "faksluexport" AS SELECT "zakazkaoprava"."id_zakazka" as idzakazka,
...

Is the performance issue on creating the view, selecting from it?
If the latter, what does explain show in the two cases?

pgsql-bugs@postgresql.org writes:
> Big diffrence in performence between 7.1 and 7.2.1. In 7.2.1 postmaster grows up to 700 MB RAM and more while in 7.1
justup to 8 MB RAM. 

You have not given nearly enough information to allow any useful
response to this complaint.

For starters, what are the schemas of the tables involved?  Have you
done ANALYZE (resp. VACUUM ANALYZE) recently?  What do you get from
EXPLAIN for the problem query on both systems?

            regards, tom lane