Re: 15x slower PreparedStatement vs raw query

Поиск
Список
Период
Сортировка
От Rick Otten
Тема Re: 15x slower PreparedStatement vs raw query
Дата
Msg-id CAMAYy4JhWn+FhwhzHUWKiDy8r62Bw4eT5QVq=kQDYyjJheV4ag@mail.gmail.com
обсуждение исходный текст
Ответ на Re: 15x slower PreparedStatement vs raw query  (Alex <cdalxndr@yahoo.com>)
Список pgsql-performance


On Tue, May 4, 2021 at 6:05 AM Alex <cdalxndr@yahoo.com> wrote:
Shouldn't this process be automatic based on some heuristics?

Saving 10ms planning but costing 14s execution is catastrophic.

For example, using some statistics to limit planner time to some percent of of previous executions. 
This way, if query is fast, planning is fast, but if query is slow, more planning can save huge execution time.
This is a better general usage option and should be enabled by default, and users who want fast planning should set the variable to use the generic plan.



"fast" and "slow" are relative things.  There are many queries that I would be overjoyed with if they completed in 5 _minutes_.  And others where they have to complete within 100ms or something is really wrong.  We don't really know what the execution time is until the query actually executes.  Planning is a guess for the best approach.

Another factor is whether the data is in cache or out on disk.  Sometimes you don't really know until you try to go get it.  That can significantly change query performance and plans - especially if some of the tables in a query with a lot of joins are in cache and some aren't and maybe some have to be swapped out to pick up others.

If you are running the same dozen queries with different but similarly scoped parameters over and over, one would hope that the system would slowly tune itself to be highly optimized for those dozen queries.  That is a pretty narrow use case for a powerful general purpose relational database though.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Alex
Дата:
Сообщение: Re: 15x slower PreparedStatement vs raw query
Следующее
От: Alex
Дата:
Сообщение: Re: 15x slower PreparedStatement vs raw query