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

On 5/5/25 13:27, Mladen Marinović wrote:
>
>
> On Mon, May 5, 2025 at 12:07 PM Achilleas Mantzios 
> <a.mantzios@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.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.
>
> 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.


ok, this is something, at least one more extreme thought ruled out. How 
about search_path ?  is this the SAME user that is issuing the 
statements in pgadmin VS pgbouncer ?

Is there a connect_query inside pgbouncer's conf ?

you have to show all configuration involved and also full logging on the 
backend for said user.


>>     Regards,
>>     Mladen Marinović
>