Re: [EXTERNAL] Performance down with JDBC 42
| От | Frits Hoogland |
|---|---|
| Тема | Re: [EXTERNAL] Performance down with JDBC 42 |
| Дата | |
| Msg-id | 062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com обсуждение исходный текст |
| Ответ на | Re: [EXTERNAL] Re: Performance down with JDBC 42 (David Rowley <dgrowleyml@gmail.com>) |
| Ответы |
RE: [EXTERNAL] Performance down with JDBC 42
|
| Список | pgsql-performance |
Very good point from Danny: generic and custom plans.
One thing that is almost certainly not at play here, and is mentioned: there are some specific cases where the planner does not optimise for the query in total to be executed as fast/cheap as possible, but for the first few rows. One reason for that to happen is if a query is used as a cursor.
(Warning: shameless promotion) I did a writeup on JDBC clientside/serverside prepared statements and custom and generic plans: https://dev.to/yugabyte/postgres-query-execution-jdbc-prepared-statements-51e2
The next obvious question then is if something material did change with JDBC for your old and new JDBC versions, I do believe the prepareThreshold did not change.
Frits Hoogland
On 5 Nov 2023, at 20:47, David Rowley <dgrowleyml@gmail.com> wrote:On Mon, 6 Nov 2023 at 08:37, Abraham, Danny <danny_abraham@bmc.com> wrote:
Both plans refer to the same DB.
JDBC is making use of PREPARE statements, whereas psql, unless you're
using PREPARE is not.#1 – Fast – using psql or old JDBC driver
The absence of any $1 type parameters here shows that's a custom plan
that's planned specifically using the parameter values given.Slow – when using JDBC 42
Because this query has $1, $2, etc, that's a generic plan. When
looking up statistics histogram bounds and MCV slots cannot be
checked. Only ndistinct is used. If you have a skewed dataset, then
this might not be very good.
You might find things run better if you adjust postgresql.conf and set
plan_cache_mode = force_custom_plan then select pg_reload_conf();
Please also check the documentation so that you understand the full
implications for that.
David
В списке pgsql-performance по дате отправления: