Hi Stephen,
Yes, The memory utilization per PostgreSQL backend process is when running queries against this tables,
For example: select * from test where num=2 and c2='abc'
When It start it doesn't consume to much memory,
But as it execute against more and more indexes the memory consumption grows
This tables should contain data, But I truncate the data of the tables because I wanted to make sure that the memory
consumptionis not relate to the data inside the table, but rather to the structure of the tables
Thanks,
Lior
-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Monday, May 27, 2013 15:43
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior (lior.ben-zeev@hp.com) wrote:
> The case which I'm seeing is that I have an empty table without any
> rows, Create table test (
> Num Integer,
> C1 character varying(512),
> C2 character varying(512),
> C3 character varying(512));
>
> I create several partial indexes on this table:
>
> Create index(index_1_c1) on test(c1) where Num=1; Create
> index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on
> test(c1) where Num=1; Create index(index_2_c2) on test(c1) where
> Num=2; ...
>
> This doesn't consume much memory on the PostgreSQL backend process,
> But if I create 500 indexes It consume several MB of memory.
When are you seeing this memory utilization..? When running a query against that table? At backend start?
> If I have 10 tables with 500 indexes each PostgreSql backend process
> consume 20MB, If I have 100 tables with 500 indexes each PostgreSQL
> backend process consume 200MB
>
> All tables are empty without data.
Are you accessing all of those tables inside of one query? Or one transaction, or..?
> What is the reason to consume so much memory for empty indexes?
I'm curious what you would expect to be happening here. We need to pull in information about the index in order to
considerit during planning.
Special-caseing empty indexes might be possible, but what's the point of having hundreds of empty indexes against a
tablein the first place?
Thanks,
Stephen
