pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Yamaji, Ryo <yamaji.ryo@jp.fujitsu.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Wed, 1 Aug 2018 10:53:03 +0300
Message-ID: <157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru> (raw)
In-Reply-To: <9A6E5062D5D4DB458C80C2B2920BD71D5C2FA8@g01jpexmbkw23>
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>



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?






view thread (107+ messages)  latest in thread

Message-ID: <157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru>
Permalink:  ../157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru/
Also on:    postgresql.org/message-id/157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: k.knizhnik@postgrespro.ru, yamaji.ryo@jp.fujitsu.com
  Subject: Re: [HACKERS] Cached plans and statement generalization
  In-Reply-To: <157e01d2-30de-0f61-bfa0-d9e1d7dbe66e@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox