why do we need two snapshots per query?
I noticed while playing around this morning that, in read committed
mode, the following query - or any other straightforward query - calls
GetSnapshotData() twice:
SELECT 1;
What happens is this:
exec_simple_query() calls analyze_requires_snapshot() on the query.
Since the query is a SELECT, this returns true, whereupon
exec_simple_query() takes a snapshot for parse analysis / planning. It
then plans the query and releases the snapshot. exec_simple_query()
then calls CreatePortal(), PortalDefineQuery(), and PortalStart(), the
last of which takes a new snapshot to run the query.
Considering that GetSnapshotData() is the number-one consumer of CPU
time on many profiling runs I've done, this seems needlessly
inefficient. Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards? Off the top of my head, I'm not exactly sure how to do
that cleanly, but it seems like it should work.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards?
Possibly, but what if planning takes a long time? Also, I think you're
ignoring the extended-query-protocol scenario, where it would be a whole
lot harder to justify keeping a snapshot from Parse through Bind and
Execute.
regards, tom lane
On Fri, Nov 11, 2011 at 10:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards?Possibly, but what if planning takes a long time? Also, I think you're
ignoring the extended-query-protocol scenario, where it would be a whole
lot harder to justify keeping a snapshot from Parse through Bind and
Execute.
In the extend query protocol scenario, it seems to me that keeping the
snapshot would be both wrong and a bad idea. It would be wrong
because the user will (I think) expect the query can see all rows that
were marked as committed prior to Execute message. It would be a bad
idea because we'd have to keep that snapshot advertised for the entire
time between Parse and Execute, even if the client was sitting there
doing nothing for a long time, which would hold back RecentGlobalXmin.
But in the simple query scenario, I think it's fine. Even if query
planning does take a long time, it's a single operation from a user
perspective. If the user sends a query and gets an answer back ten
seconds later, they don't know (and shouldn't care) whether that
happened because the query took nine seconds to plan and one second to
execute, or one second to plan and nine seconds to execute, or 50ms to
plan and 9950ms to execute. For the scenario you're talking about to
be a problem, someone would have to be expecting a query to see rows
from a transaction that committed *after* the query was sent - based,
presumably, on the knowledge, that the execution snapshot wouldn't be
taken immediately, and that the concurrent transaction would commit
meanwhile. But such a practice is flagrantly unsafe anyway, because
any optimization that makes query planning faster could break it. And
I'm not prepared to guarantee that we're never going to speed up the
optimizer.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Nov11, 2011, at 16:18 , Robert Haas wrote:
In the extend query protocol scenario, it seems to me that keeping the
snapshot would be both wrong and a bad idea. It would be wrong
because the user will (I think) expect the query can see all rows that
were marked as committed prior to Execute message. It would be a bad
idea because we'd have to keep that snapshot advertised for the entire
time between Parse and Execute, even if the client was sitting there
doing nothing for a long time, which would hold back RecentGlobalXmin.
Hm, but that'd penalize clients who use the extended query protocol, which
they have to if they want to transmit out-of-line parameters. You could
work around that by making the extended protocol scenario work like the
simply protocol scenario if the unnamed statement and/or portal is used.
Since clients presumably use pipelined Parse,Bind,Execute messages when
using the unnamed statement and portal, they're unlikely to observe the
difference between a snapshot taken during Parse, Bind or Execute.
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
On Nov11, 2011, at 16:18 , Robert Haas wrote:
In the extend query protocol scenario, it seems to me that keeping the
snapshot would be both wrong and a bad idea.
Hm, but that'd penalize clients who use the extended query protocol, which
they have to if they want to transmit out-of-line parameters. You could
work around that by making the extended protocol scenario work like the
simply protocol scenario if the unnamed statement and/or portal is used.
Since clients presumably use pipelined Parse,Bind,Execute messages when
using the unnamed statement and portal, they're unlikely to observe the
difference between a snapshot taken during Parse, Bind or Execute.
I think it would be a seriously bad idea to allow the unnamed portal to
have semantic differences from other portals. We've gotten enough flak
about the fact that it had planner behavioral differences (enough so that
those differences are gone as of HEAD).
regards, tom lane
On Nov11, 2011, at 17:06 , Tom Lane wrote:
Florian Pflug <fgp@phlo.org> writes:
On Nov11, 2011, at 16:18 , Robert Haas wrote:
In the extend query protocol scenario, it seems to me that keeping the
snapshot would be both wrong and a bad idea.Hm, but that'd penalize clients who use the extended query protocol, which
they have to if they want to transmit out-of-line parameters. You could
work around that by making the extended protocol scenario work like the
simply protocol scenario if the unnamed statement and/or portal is used.Since clients presumably use pipelined Parse,Bind,Execute messages when
using the unnamed statement and portal, they're unlikely to observe the
difference between a snapshot taken during Parse, Bind or Execute.I think it would be a seriously bad idea to allow the unnamed portal to
have semantic differences from other portals. We've gotten enough flak
about the fact that it had planner behavioral differences (enough so that
those differences are gone as of HEAD).
Oh, I missed that and worked from the assumption that we're still special-
casing the unnamed case. Since we don't, re-introducing a difference in
behaviour is probably a bad idea.
Still, optimizing only the simple protocol seems weird.
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
Still, optimizing only the simple protocol seems weird.
Would it be sane to decree that the "statement snapshot" lasts until
Sync is received, in extended query mode?
But frankly I do not like any of these proposals. Making fundamental
changes in long-established semantics in the name of squeezing out a few
cycles is the wrong way to design software.
regards, tom lane
Robert Haas <robertmhaas@gmail.com> writes:
Considering that GetSnapshotData() is the number-one consumer of CPU
time on many profiling runs I've done, this seems needlessly
inefficient. Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards? Off the top of my head, I'm not exactly sure how to do
that cleanly, but it seems like it should work.
Please refer to this thread:
http://postgresql.1045698.n5.nabble.com/One-Shot-Plans-td4488820.html
It seems one of the more prominent drawback of Simon's approach to
one-shot plans then was which snapshot it's running against, so your
proposal to optimize one-shot plan by enforcing the use of a single
snapshot looks like a step forward here.
The other problem is how to recognize a query as being a candidate for
one-shot optimization, but I guess exec_simple_query (as opposed to the
v3 protocol) applies.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Fri, Nov 11, 2011 at 2:21 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Considering that GetSnapshotData() is the number-one consumer of CPU
time on many profiling runs I've done, this seems needlessly
inefficient. Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards? Off the top of my head, I'm not exactly sure how to do
that cleanly, but it seems like it should work.Please refer to this thread:
http://postgresql.1045698.n5.nabble.com/One-Shot-Plans-td4488820.html
It seems one of the more prominent drawback of Simon's approach to
one-shot plans then was which snapshot it's running against, so your
proposal to optimize one-shot plan by enforcing the use of a single
snapshot looks like a step forward here.The other problem is how to recognize a query as being a candidate for
one-shot optimization, but I guess exec_simple_query (as opposed to the
v3 protocol) applies.
It would be nice if we could kill two birds with one stone, but I'm
not sure it we'll be that lucky. The trouble is that PortalStart()
does different things depending on what opinion ChoosePortalStrategy()
offers about the statement to be processed, and the code that sets the
snapshot for parsing and planning uses a completely separate (and
generally simpler) heuristic. Maybe there's an easy way to centralize
that decision-making; I'll have a look. If not, I'll settle for
improving the case that looks improvable.
In terms of improving things for the extended protocol, I think there
may be other ways to do that, but this particular optimization won't
apply, so it's a separate project...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Nov 11, 2011 at 7:21 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Considering that GetSnapshotData() is the number-one consumer of CPU
time on many profiling runs I've done, this seems needlessly
inefficient. Can't we arrange to retain the snapshot used for parse
analysis / planning and reuse it for the portal that we create just
afterwards? Off the top of my head, I'm not exactly sure how to do
that cleanly, but it seems like it should work.Please refer to this thread:
http://postgresql.1045698.n5.nabble.com/One-Shot-Plans-td4488820.html
It seems one of the more prominent drawback of Simon's approach to
one-shot plans then was which snapshot it's running against, so your
proposal to optimize one-shot plan by enforcing the use of a single
snapshot looks like a step forward here.
Agreed, its essentially the same thing.
If execution immediately follows planning we should recognise it and
do something about it.
Tom, in that earlier thread you said you'd be doing something in this
release about that. Can you say more about what that was, and will you
be doing it still?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes:
Tom, in that earlier thread you said you'd be doing something in this
release about that. Can you say more about what that was, and will you
be doing it still?
I think that largely supersedes what I understood your notion of a
one-shot plan to be about, though perhaps I missed something?
I don't think this has a lot to do with what Robert is on about, since
in any situation where a plan is cached for later, we surely are not
going to use the same snapshot to execute it.
regards, tom lane
On Nov11, 2011, at 19:17 , Tom Lane wrote:
But frankly I do not like any of these proposals. Making fundamental
changes in long-established semantics in the name of squeezing out a few
cycles is the wrong way to design software.
Hm, then maybe this is one of the things to put onto the next protocol
version todo list?
best regards,
Florian Pflug
On Sat, Nov 12, 2011 at 5:09 PM, Florian Pflug <fgp@phlo.org> wrote:
On Nov11, 2011, at 19:17 , Tom Lane wrote:
But frankly I do not like any of these proposals. Making fundamental
changes in long-established semantics in the name of squeezing out a few
cycles is the wrong way to design software.Hm, then maybe this is one of the things to put onto the next protocol
version todo list?
+1. I had the same thought. It seems clear that we could design this
in a way that would make it clear to the server whether we wanted to
execute immediately or only upon further instructions, but trying to
guess the user's intentions seems a little too rich.
Meanwhile, here's my attempt at fixing this for the simple query
protocol. I'm attaching two patches:
- refactor-portal-start.patch, which attempts to change the API for
PortalStart() without any behavioral change whatsoever. The idea here
is that instead of passing a snapshot to PortalStart() explicitly, we
just pass a flag saying whether or not it's OK to use the active
snapshot (versus taking a new one). This seems to fit nicely with
existing calling patterns for this function.
- just-one-snapshot.patch, which applies atop
refactor-portal-start.patch, makes use of the new API to avoid the
need for PORTAL_ONE_SELECT queries to take two snapshots. It does so
by keeping the parse/analyze snapshot around just long enough to pass
it to PortalStart(). If PortalStart() chooses to register it, then it
(or a copy of it) will be around for a while longer; otherwise, it
will be dropped immediately after PortalStart() finishes.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Nov 11, 2011 at 10:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
Tom, in that earlier thread you said you'd be doing something in this
release about that. Can you say more about what that was, and will you
be doing it still?I think that largely supersedes what I understood your notion of a
one-shot plan to be about, though perhaps I missed something?
I was looking at other use cases, specifically partitioning/partial indexes.
If we could be certain that a query was being executed immediately
then it would be possible to simplify expressions using stable
functions as if they were constants. My earlier patch did exactly
that.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes:
If we could be certain that a query was being executed immediately
... that is, with the same snapshot ...
then it would be possible to simplify expressions using stable
functions as if they were constants. My earlier patch did exactly
that.
Mph. I had forgotten about that aspect of it. I think that it's
very largely superseded by Marti Raudsepp's pending patch:
https://commitfest.postgresql.org/action/patch_view?id=649
which does more and doesn't require any assumption that plan and
execution snapshots are the same.
Now you're going to say that that doesn't help for failure to prove
partial index or constraint conditions involving stable functions,
and my answer is going to be that that isn't an interesting use-case.
Partial index conditions *must* be immutable, and constraint conditions
*should* be. As far as partitioning goes, the correct solution there
is to move the partition selection to run-time, so we should not be
contorting query semantics to make incremental performance improvements
with the existing partitioning infrastructure.
I remain of the opinion that Robert's proposal is a bad idea.
regards, tom lane
On Sun, Nov 13, 2011 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
As far as partitioning goes, the correct solution there
is to move the partition selection to run-time, so we should not be
contorting query semantics to make incremental performance improvements
with the existing partitioning infrastructure.
Agreed, but I think we need both planning and execution time
awareness, just as we do with indexonly.
That's what I'd like to be able to do: link planning and execution.
It's all very well to refuse individual cases where linkage is
required, but ISTM clear that there are many possible uses of being
able to tell whether a plan is one-shot or not and nothing lost by
allowing that information (a boolean) pass to the executor.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes:
It's all very well to refuse individual cases where linkage is
required, but ISTM clear that there are many possible uses of being
able to tell whether a plan is one-shot or not and nothing lost by
allowing that information (a boolean) pass to the executor.
It's an interconnection between major modules that IMO we don't need.
Having the executor behave differently depending on the planning path
the query took creates complexity, which creates bugs. You haven't
produced any use-case at all that convinces me that it's worth the risk;
nor do I believe there are lots more use-cases right around the corner.
regards, tom lane
On Sun, Nov 13, 2011 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
If we could be certain that a query was being executed immediately
... that is, with the same snapshot ...
then it would be possible to simplify expressions using stable
functions as if they were constants. My earlier patch did exactly
that.Mph. I had forgotten about that aspect of it. I think that it's
very largely superseded by Marti Raudsepp's pending patch:
https://commitfest.postgresql.org/action/patch_view?id=649
which does more and doesn't require any assumption that plan and
execution snapshots are the same.Now you're going to say that that doesn't help for failure to prove
partial index or constraint conditions involving stable functions,
and my answer is going to be that that isn't an interesting use-case.
Partial index conditions *must* be immutable, and constraint conditions
*should* be. As far as partitioning goes, the correct solution there
is to move the partition selection to run-time, so we should not be
contorting query semantics to make incremental performance improvements
with the existing partitioning infrastructure.I remain of the opinion that Robert's proposal is a bad idea.
Wait a minute. I can understand why you think it's a bad idea to
preserve a snapshot across multiple protocol messages
(parse/bind/execute), but why or how would it be a bad idea to keep
the same snapshot between planning and execution when the whole thing
is being done as a unit? You haven't offered any real justification
for that position, and it seems to me that if anything the semantics
of such a thing are far *less* intuitive than it would be to do the
whole thing under a single snapshot. The whole point of snapshot
isolation is that our view of the database doesn't change mid-query;
and yet you are now saying that's exactly the behavior we should have.
That seems exactly backwards to me.
I also think you are dismissing Simon's stable-expression-folding
proposal far too lightly. I am not sure that the behavior he wants is
safe given the current details of our implementation - or even with my
patch; I suspect a little more than that is needed - but I am pretty
certain it's the behavior that users want and expect, and we should be
moving toward it, not away from it. I have seen a significant number
of cases over the years where the query optimizer generated a bad plan
because it did less constant-folding than the user expected. Users do
not walk around thinking about the fact that the planner and executor
are separate modules and therefore probably should use separate
snapshots. They expect their query to see a consistent view of the
database. Period.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Nov 13, 2011 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I remain of the opinion that Robert's proposal is a bad idea.
Wait a minute. I can understand why you think it's a bad idea to
preserve a snapshot across multiple protocol messages
(parse/bind/execute), but why or how would it be a bad idea to keep
the same snapshot between planning and execution when the whole thing
is being done as a unit? You haven't offered any real justification
for that position,
It's not hard to come by: execution should proceed with the latest
available view of the database.
and it seems to me that if anything the semantics
of such a thing are far *less* intuitive than it would be to do the
whole thing under a single snapshot.
In that case you must be of the opinion that extended query protocol
is a bad idea and we should get rid of it, and the same for prepared
plans of all types. What you're basically proposing is that simple
query mode will act differently from other ways of submitting a query,
and I don't think that's a good idea. It might be sane if planning
could be assumed to take zero time, but that's hardly true.
I also think you are dismissing Simon's stable-expression-folding
proposal far too lightly. I am not sure that the behavior he wants is
safe given the current details of our implementation - or even with my
patch; I suspect a little more than that is needed - but I am pretty
certain it's the behavior that users want and expect, and we should be
moving toward it, not away from it. I have seen a significant number
of cases over the years where the query optimizer generated a bad plan
because it did less constant-folding than the user expected.
This is just FUD, unless you can point to specific examples where
Marti's patch won't fix it. If that patch crashes and burns for
some reason, then we should revisit this idea; but if it succeeds
it will cover more cases than plan-time constant folding could.
One of the reasons I don't want to go this direction is that it would
re-introduce causes of extended query protocol having poor performance
relative to simple protocol. That's not something that users find
intuitive or desirable, either.
regards, tom lane
Tom Lane wrote:
Robert Haas writes:
I can understand why you think it's a bad idea to preserve a
snapshot across multiple protocol messages (parse/bind/execute),
but why or how would it be a bad idea to keep the same snapshot
between planning and execution when the whole thing is being done
as a unit? You haven't offered any real justification for that
position,It's not hard to come by: execution should proceed with the latest
available view of the database.
I don't think that stands as an intuitively obvious assertion. I
think we need to see the argument which leads to that conclusion.
and it seems to me that if anything the semantics of such a thing
are far *less* intuitive than it would be to do the whole thing
under a single snapshot.In that case you must be of the opinion that extended query
protocol is a bad idea and we should get rid of it, and the same
for prepared plans of all types. What you're basically proposing is
that simple query mode will act differently from other ways of
submitting a query, and I don't think that's a good idea.
In what way would that difference be user-visible?
One of the reasons I don't want to go this direction is that it
would re-introduce causes of extended query protocol having poor
performance relative to simple protocol. That's not something that
users find intuitive or desirable, either.
If the simple protocol can perform better than the extended protocol,
it hardly seems like a good idea to intentionally cripple the fast
one to keep them at the same performance. It seems like it would be
better to document the performance difference so that people can
weigh the trade-offs.
-Kevin
Import Notes
Resolved by subject fallback
