Re: PostgreSQL Process memory architecture

Lior,

* Ben Zeev, Lior (lior(dot)ben-zeev(at)hp(dot)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 consider it during planning.
Special-caseing empty indexes might be possible, but what's the point of
having hundreds of empty indexes against a table in the first place?

Thanks,

Stephen