On Mon, May 5, 2025 at 2:38 PM SERHAD ERDEM <serh...@hotmail.com> wrote:
> Hi , you had better try vacuum analyze for the whole db , pgbouncer > connection layer can not causeslow queries. > I did that already. But the slow query is the consequence of the different plan, not the statistics. > ------------------------------ > *From:* Mladen Marinović <ma...@kset.org> > *Sent:* Monday, May 5, 2025 12:27 PM > *To:* Achilleas Mantzios <a.mantz...@cloud.gatewaynet.com> > *Cc:* pgsql-general@lists.postgresql.org < > pgsql-general@lists.postgresql.org> > *Subject:* Re: Different execution plans in PG17 and pgBouncer... > > > > On Mon, May 5, 2025 at 12:07 PM Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com> wrote: > > > On 5/5/25 11:00, Mladen Marinović wrote: > > > > On Mon, May 5, 2025 at 11:24 AM Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com> wrote: > > > On 5/5/25 09:52, Mladen Marinović wrote: > > 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. > > > That's a huge jump, I hope you guys did extensive testing of your app. In > which language is your app written? If java, then define prepareThreshold=0 > in your jdbc and set max_prepared_statements = 0 in pgbouncer. > > Mainly python, but the problem was noticed in a java service. > Prepare treshold was already set to 0. We changed the max_prepared_statements > to 0 from the default (200) but no change was noticed. > > How about search paths ? any difference on those between the two runs ? Do > you set search_path in pgbouncer ? what is "cca." btw ? > > > 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? > > > Does this spawn any connections (such as dblink) ? are there limits per > user/db pool_size in pgbouncer ? > > No additional connection nor dbling. Just plain SQL (CTE, SELECT, INSERT, > UPDATE, DELETE,...) There are limits, but they are not hit. The query just > uses a different plan and runs slower because of that. > > Pgbouncer, in contrast to its old friend PgPool-II is completely passive, > just passes through SQL to the server as fast as possible as it can. But I > am sure you know that. Good luck, keep us posted! > > Yes, that is what puzzles me. > > What is the pgbouncer's timeout in the server connections ? > > How about "idle in transaction" ? do you get any of those? What's the > isolation level ? > > How about the user ? is this the same user doing pgadmin queries VS via > the app ? > > Can you identify the user under which the problem is manifested and : > > ALTER user "unlucky_user" SET log_statement = 'all'; > > ALTER user "unlucky_user" SET log_min_duration_statement = 0; -- to help > you debug the prepared statements .. just in case , and other stuff not > printed by log_statement = all. > > None of those parameters should affect the fact that when issuing the > explain select query (the statement is not prepared) from psql directly > gives a different result than issuing it over the pgbouncer connection. The > result is repeatable. > > We have rolled back pgbouncer to 1.12. and it seems the problem persists. > This is one of the weirdest things I have ever seen with PostgreSQL. > > > Regards, > Mladen Marinović > >
