Upgrading JDBC driver from 7.3 to 8.4 affected execution plans
От | Boris |
---|---|
Тема | Upgrading JDBC driver from 7.3 to 8.4 affected execution plans |
Дата | |
Msg-id | 9c7afd41-61f0-4acb-bc14-bb10aeaf395f@d17g2000yqm.googlegroups.com обсуждение исходный текст |
Ответы |
Re: Upgrading JDBC driver from 7.3 to 8.4 affected execution
plans
|
Список | pgsql-jdbc |
Hi all, I am trying to understand an interesting side effect of our recent JDBC driver upgrade (7.3 -> 8.4.701). Here is what I am seeing: We use prepared statements to construct a multi inner/outer join (~ 15 joins, all on primary keys) query which brings back 0 or 1 raw. With old driver, a query would take single digit milliseconds on average when executed from java code. With new driver, I noticed that the load on db server skyrocketed and all those queries take seconds to execute. The reason is that the optimizer chooses merge joins instead of nested loops. When I turn merge/hash joins off in postgresql.conf, all went back to normal, queries are now executed very fast. Another interesting point is that, when I paste the slow query into psql, and explain/analyze it, bad plan is chosen (like observed from the code with new driver). Any ideas? I'd be happy to provide more details if needed. Is the issue somehow caused by the fact that 8.4 driver uses real server side prepared statements vs the old one that didn't? Cheers, Boris
В списке pgsql-jdbc по дате отправления: