Re: Different execution plans in PG17 and pgBouncer...

On 5/5/25 11:00, Mladen Marinović wrote:
>
>
> On Mon, May 5, 2025 at 11:24 AM Achilleas Mantzios 
> <a.mantzios@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.

> Regards,
> Mladen Marinović