Re: Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102
От | Vladimir Sitnikov |
---|---|
Тема | Re: Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 |
Дата | |
Msg-id | CAB=Je-Gp2NmGs5KDBwUGL36UGko8x89n=VQjLS+u2GYSKwjKSA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102 (Dave Cramer <pg@fastcrypt.com>) |
Ответы |
Re: Re: 9.4-1207 behaves differently with server side prepared
statements compared to 9.2-1102
|
Список | pgsql-jdbc |
>I can't imagine why that would even happen. That can happen in case backend uses "bad plan" for server-prepared statement. Here are more details: http://www.postgresql.org/docs/9.4/static/sql-prepare.html As far as I remember, backend can switch plan on 5th or 6th execution of server-prepared statement. >PG DOC: If a prepared statement is executed enough times, the server may eventually decide to save and re-use a genericplan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters In other words, even server-prepared statements can behave differently from one execution to another. I've seen a couple of times when a query was fast "the first 5 times", then backend (9.4) switched to much slower plan. That happened with exactly the same input value. The resolution for me was to fix query plan as desired (add offset 0 here and there) so the join order was specific. On the other hand, I've seen impressive performance improvements for long queries that take much longer to plan than to execute. Common wisdom is to hide long SQL into stored procedures (they have transparent statement cache too), however it can't easily be done for existing application. Vladimir
В списке pgsql-jdbc по дате отправления: