Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.60.0.2.21\))
Subject: Re: Planning performance problem (67626.278ms)
From: Manuel Weitzman <manuelweitzman@gmail.com>
In-Reply-To: <767433.1619015825@sss.pgh.pa.us>
Date: Mon, 14 Jun 2021 13:25:38 -0400
Cc: David Rowley <dgrowleyml@gmail.com>,
 Jeremy Schneider <schnjere@amazon.com>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
 Andres Freund <andres@anarazel.de>
Content-Transfer-Encoding: quoted-printable
Message-Id: <EFE95962-F088-45E2-BD13-AA5012829A69@gmail.com>
References: <6d58f68e-ed1a-c48e-1440-691554f6e1e7@9livesdata.com>
 <CAFj8pRDDnOVNcU7dx4+oKH0x4-DsmL3XRWppQ8Cf5wwJ=G2fPg@mail.gmail.com>
 <20190408144210.GG10080@telsasoft.com>
 <b145b428-3351-ec86-d522-100a5bf8ded7@amazon.com>
 <20190408232644.r6xixkeulsmomwfj@alap3.anarazel.de>
 <3f5e5673-06aa-bf7c-cdb7-0e053c45ba26@amazon.com>
 <CAApHDvq9o1J45is6w-3Gv1UgtuDQ4eQP4UhFPQSnQmEH_EijvQ@mail.gmail.com>
 <767433.1619015825@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/EFE95962-F088-45E2-BD13-AA5012829A69%40gmail.com>
Precedence: bulk

> However, I'm skeptical that any problem actually remains in
> real-world use cases.

Hello Tom,

We also had some issues with planning and get_actual_variable_range(). =
We
actually found some interesting behaviour that probably requires an eye =
with
better expertise in how the planner works.
For the example being discussed you can add some joins into the equation =
and
planning times deteriorate quite a bit.
I'll just skip posting the first executions as it is already established =
that
a subsequent one will be faster.


create table b (b int primary key, a int references a(a))
with (autovacuum_enabled=3Doff);

insert into a select x from generate_series(1,10000000) x;
insert into b select x, x from generate_series(1,10000000) x;
create index b_a_idx on b(a);
analyze a, b;


For our case a rollback of a bulk insert causes bloat on the index.


begin;
insert into a select x from generate_series(10000001,20000000) x;
rollback;

explain (analyze, buffers)
select * from a
join b on (b.a =3D a.a)
where b.a in (1,100,10000,1000000,1000001);

 Planning:
   Buffers: shared hit=3D9 read=3D27329
 Planning Time: 134.560 ms
 Execution Time: 0.100 ms


I see a lot of buffers being read for some reason (wasn't this fixed?). =
And
times are slow too. But it get's worse with each join added to the =
select.


explain (analyze, buffers)
select * from a
join b b1 on (b1.a =3D a.a)
join b b2 on (b2.a =3D a.a)
where b1.a in (1,100,10000,1000000,1000001);

 Planning:
   Buffers: shared hit=3D38 read=3D81992
 Planning Time: 312.826 ms
 Execution Time: 0.131 ms

Just add a few more joins and it is a recipe for disaster.
Apparently, the planner isn't reusing the data boundaries across =
alternative
plans. It would be nicer if the planner remembered each column =
boundaries
for later reuse (within the same planner execution).

Another thing that worries me is that even the second run has faster =
planning
it is still way slower than the case without lots of bloat in the index. =
And
I don't think this is just an edge case. Rollbacks on bulk inserts can =
be
quite common, and joins are expected in a SQL database.

We had downtime due to how the planner works on this case. Unfortunately
setting more aggressive vacuum settings won't fix our problems. Most of =
the
read queries are being issued to a replica. When the issues with the =
planner
start happening, CPU usage on that node goes to 100% which interferes =
with the
replication process.
This means the replica cannot get to a new checkpoint with a new live
max value in the index nor can it delete the bloat that vacuum has =
already
cleaned on the leader server.

Oh, by the way, we're running version 13.2


Regards,

Manuel