>[email protected] writes:
>> the poster who started this thread had a query where the parsing phase
>> took significantly longer than the planning stage.
> That was an anecdote utterly unsupported by evidence.
regards, tom lane
The issue of prepared statements having atrocious query plans has hit me again.
I feel very strongly about this topic and the need for Postgres to have an
option that allows for prepared statements to re-plan based on the inputs that
works and is user friendly. Pardon my bluntness, but in the current situation
the system is brain dead in many important use cases and data sets.
I believe my statement referenced above was about parsing time to the remaining
time, not parsing compared to planning. But either way, its a minor detail,
and its not important to justify the need for the enhancement here.
Yeah, its anecdotal from your perspective. Go ahead and ignore all that if you
wish.
**** I am making several points in this message that are independent of such
evidence, IMHO.
I have tried to rearrange this message so that the anecdotal narrative is at
the end, after the first dashed line.
Unnamed prepared statements do solve much of the problem in theory, since the
most common issue is typically poor execution plans or a lack of ability to
cleanly deal with SQL injection and write less bug prone client code. Parsing
being very expensive is more rare. But there IS a performance savings that is
not insignificant for many workloads to be had by avoiding the utterly
avoidable parsing.
HOWEVER:
What is overlooked WRT unnamed prepared statements, is that they are hard to
use and changing client code or behavior is difficult, error prone, and
sometimes impossible. Not all client APIs play nice with them at the moment
(see Postgres' JDBC). The behavior has some global tweaks, but these are
useless in many situations where you need behavior that varies.
Every time the answer to a problem is to change the client behavior, I ask
myself if the DB could have a better default or configuration parameter so that
clients don't have to change. Some database instances have dozens of
applications and hundreds of client types including ad-hoc usage. Some client
code is legacy code that simply can't be changed.
Changing the clients is a lot harder than changing a db parameter, or
configuring a new default for a particular db user. If the client must change,
adding something like SET prepare_level = 'parse_only' is the least intrusive
and easiest to test - but I stress again that in many real-world cases the
client is not flexible.
A session-level parameter that controls prepared statement behavior defaults
(never cache by default? parse cache only? parse cache and plan cache?) would
be a blessing. A DBA could administer a fix to certain problems without having
to force clients to change behavior or wait for new client API versions with
fixes.
That reminds me, isn't there a global parameter that can force no prepared
statements to be cached, does that make them all behave as if they are unnamed?
Or are they simply re-created each time? I believe I tried this in the past
and the prepared statements were unable to use the parameter values for
partition table selection, suggesting the latter.
Typically, I run into the issue with queries in a back-end process that
operates on large data sets or partitioned tables. Prepared statements
essentially kill performance by several orders of magnitude (think, scan 1
versus scan 5000 partition tables). However, my recent issue is brutally simple.
I need to have an auto-complete web form, and thus need a query with a portion
like
WHERE name LIKE 'userenteredtext%'
Thus, I make an index with varchar_pattern_ops and off we go! ... Or not.
Works fine with explicit queries, but not a prepared query. Unfortunately,
this is highly prone to SQL injection, and the industry standard way to deal
with this is by parameterization.
http://www.owasp.org/index.php/Guide_to_SQL_Injection
(that site is a weath of information, tools, and tests on the topic, for
example: http://www.owasp.org/index.php/Testing_for_SQL_Injection).
Prepared statements are a blessing from a client code perspective, preventing
all sorts of bugs and catching many others early, on the client side. Not
being able to use them because it causes the database to execute very bad query
plans is a flaw in the database, not the client.
------------------------------------------------------------------------------------
Unnamed prepared statements did not work for me when I tried them as a solution
(8.3.2, supposedly after the fix). I was in a hurry to fix my issue, and just
moved on when they were plainly not working. It is possible I did something
wrong back then. They are also poorly supported by many client APIs -- when
they did not work for me, I supposed it was a JDBC issue or perhaps user error,
but maybe it was server side? The documentation on both sides is not entirely
clear on what should really be happening. And I could not figure out how to
debug where the problem was. Can you even test an unnamed prepared statement
in psql?
I had no time to waste and changed a lot of the client code instead, and have
since been less interested until the topic came up in this thread, and then
after letting this message sit as a draft for a month, ran into the
"varchar_pattern_ops + prepared statement = index, what index?" Issue.
--------
On queries that are overly parse-heavy:
It is very difficult to tease apart parse time from plan time in Postgres
(genrally speaking, getting performance data from Postgres is much harder than
the commercial DB's I've used). However, my experience with commercial DBs
that have running counters of time spent in various operations is that parsing
can be upwards of 50% of the total CPU usage with some workloads. Decision
Support type stuff where many light-weight queries are executed, is where I
have seen that with Oracle. In Oracle you can find out exactly how much of CPU
time was spent parsing versus planning versus other stuff in aggregate over a
time interval. I don't suspect that Postgres has a parser that is orders of
magnitude faster. But its true I don't have direct evidence to share right now
teasing apart plan from parse time.
So yes, unnamed prepared statements are potentially part (but not all) of a
solution, provided there was good control over their use in client API's (which
there isn't and won't be, since they are non-standard), or there is a useful
non-global way to configure them.
Planning is long on most queries that take long to parse, but this will vary.
(We have several queries that access tables with either only primary key
indexes or no indexes, lots of embedded 'stuff' that is naturally more parser
than planner heavy like CASE statements and value constraints/checks/modifiers
on columns without indexes and not in the where clause, and some are
approximately 2k character behemoths).
-----
On Performance Improvements gained by avoiding prepared statements:
I gained about a factor of 5x to 50x in performance by changing a lot of code
to avoid prepared statements. Some of the worst cases involved partitioned
tables, where planning issues are particularly troublesome. (lets scan 1000
tables for no good reason! Hooray!)
I never posted here about my struggles with prepared statements and execution
performance, I knew it wasn't going to change and I had to fix the problem in a
few days time. One of the queries changed had about 7 sub-selects in it, but
the eventual plan can be very fast for some cases, returning almost no data,
and we run this query repeatedly with slightly different parameters. So with
some parameters the execution time dominates by orders of magnitude, and for
most parameter combinations the execution time is almost none of it. Of
course, now we just write a new query for all the variants, else the
performance is unacceptable. This is not too difficult of a change because
these are not a SQL injection worry, although it has complicated the client
code and test cases.
---
Being able to avoid these problems and let programmers use prepared statements
would be a good thing, and so I think a solution more useable and flexible than
the current unnamed prepared statements would be great. And if the avoidable
and redundant re-parsing can be avoided too, its win-win.
Being able to cut parsing out of the loop in many cases to improve performance,
should be able to stand up on its own as a legitimate improvement. If it is
permanently bound to planning, it is permanently bound to significant caveats.
Decoupling the two and providing a means of control over these WRT prepared
statements and related features has much merit IMO.
