Subject: Re: [HACKERS] Cached plans and statement generalization
To: "Yamaji, Ryo" <yamaji.ryo@jp.fujitsu.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: 
 <CA+TgmoZAHc4wqL3HvVmTd87s=bobp_8_7R9H=B_kH6q4GcA=Yg@mail.gmail.com>
 <4bd78cd4-1e64-5294-2eb8-3d061b20288f@postgrespro.ru>
 <CA+TgmoaCAoSrU9TL8t5J3YP9xp1P-0f5vySy74Rd5kPKLNuqfQ@mail.gmail.com>
 <8eed9c23-19ba-5404-7a9e-0584b836b3f3@postgrespro.ru>
 <283dfb96-48c2-1a87-90ab-97ca87b237fe@postgrespro.ru>
 <CAEepm=2rvscv5kQppLsO6SGzEpKD6v+WB7ikO+uGniQXqQcRjQ@mail.gmail.com>
 <b542f0ee-02be-01cc-d8ee-af1a5a1718c3@postgrespro.ru>
 <e6ced412-75ee-b4a8-d61d-68fed2cfe8cc@postgrespro.ru>
 <CAB7nPqS+2ZRcZZGMJOAYON2h_GOL8G2QhjmbZT__pPuZ8feA0A@mail.gmail.com>
 <a47db8e0-16c0-32ef-60b1-6ee3ebac5f3e@postgrespro.ru>
 <20180106225118.GS2416@tamriel.snowman.net>
 <CAEepm=1MyFoM7VS+ed3BS3PWvSz_oU_3_uMESdQCozku1+K1SQ@mail.gmail.com>
 <20638bbb-2744-a4ba-116d-084e9c6520bf@postgrespro.ru>
 <9A6E5062D5D4DB458C80C2B2920BD71D5C2FA8@g01jpexmbkw23>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru>
Date: Wed, 1 Aug 2018 10:53:03 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <9A6E5062D5D4DB458C80C2B2920BD71D5C2FA8@g01jpexmbkw23>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 31.07.2018 12:12, Yamaji, Ryo wrote:
>
> 3. I confirmed the transition of the amount of the memory when it tried to prepare query
> of the number that exceeded the value specified for autoprepare_limit.
> [autoprepare_limit=1 and execute 10 different queries]
> 	plan cache context: 1032 used
> 	plan cache context: 39832 used
> 	plan cache context: 78552 used
> 	plan cache context: 117272 used
> 	plan cache context: 155952 used
> 	plan cache context: 194632 used
> 	plan cache context: 233312 used
> 	plan cache context: 272032 used
> 	plan cache context: 310712 used
> 	plan cache context: 349392 used
> 	plan cache context: 388072 used
>
> I feel the doubt in an increase of the memory utilization when I execute a lot of
> query though cached query is one (autoprepare_limit=1).
> This behavior is correct?

I failed to reproduce the problem.
I used the following non-default configuration parameters:

autoprepare_limit=1
autoprepare_threshold=1

create dummy database:

create table foo(x integer primary key, y integer);
insert into foo values (generate_series(1,10000), 0);

and run different queries,  like:

postgres=# select *  from foo where x=1;
postgres=# select *  from foo where x+x=1;
postgres=# select *  from foo where x+x+x=1;
postgres=# select *  from foo where x+x+x+x=1;
...

and check size of CacheMemoryContext using gdb - it is not increased.

Can you please send me your test?