Caching query plan costs
This email thread from last year:
/messages/by-id/CAMjNa7cC4X9YR-vAJS-jSYCajhRDvJQnN7m2sLH1wLh-_Z2bsw@mail.gmail.com
got stuck on when to check for certain optimizations in the planner. It
was discussed that knowing a plan was going to be expensive _before_
planning could help determine when to try certain expensive
optimizations.
What if we globally or locally cache the _cost_ of plans, so we can
consult that cache before planning and enable certain optimizations? It
also might be useful to cache some of the unexpected things found during
execution, e.g. inaccurate cost estimates.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Bruce Momjian <bruce@momjian.us> writes:
What if we globally or locally cache the _cost_ of plans, so we can
consult that cache before planning and enable certain optimizations?
But what would you use as cache key? And how's this help if we haven't
seen a similar query before in the session?
regards, tom lane
On Mon, Sep 3, 2018 at 01:30:33PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
What if we globally or locally cache the _cost_ of plans, so we can
consult that cache before planning and enable certain optimizations?But what would you use as cache key? And how's this help if we haven't
Uh, I assume we would do what pg_stat_statements does and remove the
constants an hash that.
seen a similar query before in the session?
Well, if it was global we could use output from another session.
I guess my point is that this only used to turn on micro-optimizations
and maybe parallelism and JIT, so it doesn't have to be 100% accurate.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On September 3, 2018 11:33:35 AM PDT, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Sep 3, 2018 at 01:30:33PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
What if we globally or locally cache the _cost_ of plans, so we can
consult that cache before planning and enable certainoptimizations?
But what would you use as cache key? And how's this help if we
haven't
Uh, I assume we would do what pg_stat_statements does and remove the
constants an hash that.
That's not particularly cheap... Constants heavily influence planning choices, so I don't think they actually could be removed.
seen a similar query before in the session?
Well, if it was global we could use output from another session.
I guess my point is that this only used to turn on micro-optimizations
and maybe parallelism
What kind of micro opts are you thinking of? The cases I remember are more in the vein of doing additional complex optimizations (join removal, transforming ORs into UNION, more complex analysis of predicates...).
Parallelism would definitely benefit from earlier knowledge, although I suspect some base rel analysis might be more realistic, because it's far from guaranteed that queries are ever repeated in a similar enough manner.
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
On September 3, 2018 11:33:35 AM PDT, Bruce Momjian <bruce@momjian.us>
wrote:On Mon, Sep 3, 2018 at 01:30:33PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
What if we globally or locally cache the _cost_ of plans, so we
can consult that cache before planning and enable certainoptimizations?
But what would you use as cache key? And how's this help if we
haven't
Uh, I assume we would do what pg_stat_statements does and remove the
constants an hash that.That's not particularly cheap... Constants heavily influence planning
choices, so I don't think they actually could be removed.
Oh.
seen a similar query before in the session?
Well, if it was global we could use output from another session.
I guess my point is that this only used to turn on
micro-optimizations and maybe parallelismWhat kind of micro opts are you thinking of? The cases I remember
are more in the vein of doing additional complex optimizations (join
removal, transforming ORs into UNION, more complex analysis of
predicates...).Parallelism would definitely benefit from earlier knowledge, although
I suspect some base rel analysis might be more realistic, because it's
far from guaranteed that queries are ever repeated in a similar enough
manner.
Yes, no question that we would need something that could detect a
sufficient percentage of previous queries.
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
Well, as I remember, we are considering disabling JIT in PG 11 because
of the use of fixed costs to trigger it. Could executor information
help decide to use JIT?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On 09/03/2018 08:56 PM, Bruce Momjian wrote:
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
On September 3, 2018 11:33:35 AM PDT, Bruce Momjian <bruce@momjian.us>
wrote:On Mon, Sep 3, 2018 at 01:30:33PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
What if we globally or locally cache the _cost_ of plans, so we
can consult that cache before planning and enable certainoptimizations?
But what would you use as cache key? And how's this help if we
haven't
Uh, I assume we would do what pg_stat_statements does and remove the
constants an hash that.That's not particularly cheap... Constants heavily influence planning
choices, so I don't think they actually could be removed.Oh.
Yeah, it doesn't really tell you the cost for the plan, because a single
query string may use vastly different plans for different constants.
Which pretty much is why we have pg_stat_plans.
Imagine a query that matches 99% of the table for one value and 1% for
another one. That's going to produce rather different plans for each
(say, seqscan vs. index scan), with very different costs.
seen a similar query before in the session?
Well, if it was global we could use output from another session.
I guess my point is that this only used to turn on
micro-optimizations and maybe parallelismWhat kind of micro opts are you thinking of? The cases I remember
are more in the vein of doing additional complex optimizations (join
removal, transforming ORs into UNION, more complex analysis of
predicates...).Parallelism would definitely benefit from earlier knowledge, although
I suspect some base rel analysis might be more realistic, because it's
far from guaranteed that queries are ever repeated in a similar enough
manner.Yes, no question that we would need something that could detect a
sufficient percentage of previous queries.and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
Well, as I remember, we are considering disabling JIT in PG 11 because
of the use of fixed costs to trigger it. Could executor information
help decide to use JIT?
Isn't that issue more about what is the right default threshold, rather
than using fixed costs in principle?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-09-03 14:56:28 -0400, Bruce Momjian wrote:
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
Well, as I remember, we are considering disabling JIT in PG 11 because
of the use of fixed costs to trigger it. Could executor information
help decide to use JIT?
I don't think so. The issues with JIT planning are more that it's
costing is simplistic (for good-ish reason, to avoid increasing the
number of plans), and that there's no caching (lots of infrastructure
work needed).
Greetings,
Andres Freund
On Mon, Sep 3, 2018 at 02:53:59PM -0700, Andres Freund wrote:
On 2018-09-03 14:56:28 -0400, Bruce Momjian wrote:
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
Well, as I remember, we are considering disabling JIT in PG 11 because
of the use of fixed costs to trigger it. Could executor information
help decide to use JIT?I don't think so. The issues with JIT planning are more that it's
costing is simplistic (for good-ish reason, to avoid increasing the
number of plans), and that there's no caching (lots of infrastructure
work needed).
Uh, yeah, that was my question. If we knew the cost was high before we
plan, could we realistically increase the number of plans to avoid the
cost-trigger issue?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +On September 3, 2018 3:01:29 PM PDT, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Sep 3, 2018 at 02:53:59PM -0700, Andres Freund wrote:
On 2018-09-03 14:56:28 -0400, Bruce Momjian wrote:
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
Well, as I remember, we are considering disabling JIT in PG 11
because
of the use of fixed costs to trigger it. Could executor
information
help decide to use JIT?
I don't think so. The issues with JIT planning are more that it's
costing is simplistic (for good-ish reason, to avoid increasing the
number of plans), and that there's no caching (lots of infrastructure
work needed).Uh, yeah, that was my question. If we knew the cost was high before we
plan, could we realistically increase the number of plans to avoid the
cost-trigger issue?
I think there are much more pressing / more general things to do. Caching of JITed "hunks" and scaling the cost with the number of JITed functions rather than one global cost. Having to run queries multiple times for good plans just isn't that interesting IMO. Especially for analytics queries, where JIT is interesting.
Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
On Mon, Sep 3, 2018 at 04:13:40PM -0700, Andres Freund wrote:
On September 3, 2018 3:01:29 PM PDT, Bruce Momjian <bruce@momjian.us>
wrote:On Mon, Sep 3, 2018 at 02:53:59PM -0700, Andres Freund wrote:
On 2018-09-03 14:56:28 -0400, Bruce Momjian wrote:
On Mon, Sep 3, 2018 at 11:42:31AM -0700, Andres Freund wrote:
and JIT, so it doesn't have to be 100% accurate.
JIT decision is done after main planning, so we know the cost.
I don't think so. The issues with JIT planning are more that it's
costing is simplistic (for good-ish reason, to avoid increasing
the number of plans), and that there's no caching (lots of
infrastructure work needed).Uh, yeah, that was my question. If we knew the cost was high before
we plan, could we realistically increase the number of plans to avoid
the cost-trigger issue?I think there are much more pressing / more general things to
do. Caching of JITed "hunks" and scaling the cost with the number
of JITed functions rather than one global cost. Having to run
queries multiple times for good plans just isn't that interesting
IMO. Especially for analytics queries, where JIT is interesting.
I agree it isn't useful for JIT alone but if it can be used for multiple
purposes, it might be worth it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +