MIME-Version: 1.0
From: =?UTF-8?Q?Mladen_Marinovi=C4=87?= <marin@kset.org>
Date: Mon, 5 May 2025 10:52:54 +0200
Message-ID: <CAHjkqPQFy2GnzRQHSkc8FXJdOYEV46cTQ9KjHXDUctCMHvveBw@mail.gmail.com>
Subject: Different execution plans in PG17 and pgBouncer...
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dc89d406345fa0b4"
Archived-At: <https://www.postgresql.org/message-id/CAHjkqPQFy2GnzRQHSkc8FXJdOYEV46cTQ9KjHXDUctCMHvveBw%40mail.gmail.com>
Precedence: bulk

--000000000000dc89d406345fa0b4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

We recently migrated our production instances from PG11 to PG17. While
doing so we upgraded our pgBouncer instances from 1.12 to 1.24. As
everything worked on the test servers we pushed this to production a few
weeks ago. We did not notice any problems until a few days ago (but the
problems were here from the start). The main manifestation of the problems
is a service that runs a fixed query to get a backlog of unprocessed data
(limited to a 1000 rows). When testing the query using pgAdmin connected
directly to the database we get a result in cca. 20 seconds. The same query
runs for 2 hours when using pgBouncer to connect to the same database.

The more interesting part is that when we issue an explain of the same
query we get different plans. We did this a few seconds apart so there
should be no difference in collected statistics. We ruled out prepared
statements, as we suspected the generic plan might be the problem, but it
is not. Is there any pgBouncer or PG17 parameter that might be the cause of
this?

Regards,
Mladen Marinovi=C4=87

--000000000000dc89d406345fa0b4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>We recently migrated our=
 production instances from PG11 to PG17. While doing so we upgraded our pgB=
ouncer instances from 1.12 to 1.24. As everything worked on the test server=
s we pushed this to production a few weeks ago. We did not notice any probl=
ems until a few days ago (but the problems were here from the start). The m=
ain manifestation of the problems is a service that runs a fixed query to g=
et a backlog of unprocessed data (limited to a 1000 rows). When testing the=
 query using pgAdmin connected directly to the database we get a result in =
cca. 20 seconds. The same query runs for 2 hours when using pgBouncer to co=
nnect to the same database.</div><div><br></div><div>The more interesting p=
art is that when we issue an explain of the same query we get different pla=
ns. We did this a few seconds apart so there should be no difference in col=
lected statistics. We ruled out prepared statements, as we suspected the ge=
neric plan might be the problem, but it is not. Is there any pgBouncer or P=
G17 parameter that might be the cause of this?</div><div><br></div><div>Reg=
ards,</div><div>Mladen Marinovi=C4=87</div></div>

--000000000000dc89d406345fa0b4--