From: Alex <cdalxndr@yahoo.com>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: 15x slower PreparedStatement vs raw query
Date: Tue, 04 May 2021 13:59:16 +0000
Message-ID: <6cd0e931a59e1.4c58a67a1e5f6@yahoo.com> (raw)
References: <3f8c88d8a6ef9.d82fa04cf610d.ref@yahoo.com>
<3f8c88d8a6ef9.d82fa04cf610d@yahoo.com>
<20210503201811.GA27406@telsasoft.com>
<49daf090ac5b1.96e1ada6021ee@yahoo.com>
<CAMAYy4JhWn+FhwhzHUWKiDy8r62Bw4eT5QVq=kQDYyjJheV4ag@mail.gmail.com>
"Powerful general purpose relational database" but not smart...
I propose a feature to use information from previously executed queries to
adjust the query plan time accordingly.
Reusing the same generic plan may and will lead to very long execution
times.
Rick Otten wrote:
On Tue, May 4, 2021 at 6:05 AM Alex <cdalxndr@yahoo.com
<mailto: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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cdalxndr@yahoo.com, rottenwindfish@gmail.com, pryzby@telsasoft.com
Subject: Re: 15x slower PreparedStatement vs raw query
In-Reply-To: <6cd0e931a59e1.4c58a67a1e5f6@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox