[HN Gopher] Working around a case where the Postgres planner is ...
___________________________________________________________________
Working around a case where the Postgres planner is "not very
smart"
Author : kmdupree
Score : 45 points
Date : 2021-08-02 18:34 UTC (4 hours ago)
(HTM) web link (heap.io)
(TXT) w3m dump (heap.io)
| fabian2k wrote:
| That's an interesting limitation I didn't know. Though I haven't
| really relied on index-only scans much so far, the limitations
| regarding the visibility map seemed a bit too hard to judge to
| me. I assume that this works usually, but it's always a bit
| intimidating when a feature talks about limitations in DB
| internal data structures you didn't even know existed.
|
| What surprises me here a bit is that this only provides a factor
| of two improvement. I would have expected the index to be much
| smaller than the table, though they include a bunch of columns in
| the index. At that ratio I'd be a bit worried about consuming a
| serious amount of space for that speedup, but that's impossible
| to judge without knowing the details. And if the performance is
| important enough here of course this is still worth it even if
| the index is large.
| kmdupree wrote:
| Thanks for reading!
|
| I hear you on visibility maps being intimidating. In practice,
| I haven't seen any cases where visibility map issues have
| prevented an index-only scan. But we did initially think that
| the visibility map was to blame for what we were seeing!
|
| RE space of the index: it cost us about 1% of the free disk
| space on our workers. It was worth it for this particular
| feature.
| infogulch wrote:
| SQLite has a nice solution to this problem: you can pin queries
| to use specific plans. By strategically controlling when
| statistics are redone, you can optimize a database for a specific
| set of queries. This does have the downside that it doesn't react
| to changes dynamically, but that is a good tradeoff in many
| circumstances.
| FabHK wrote:
| A long time ago, our team tried to implement basically a
| versioned time-series key-value store (ie, you have many keys
| with daily values, and in addition to key and value it also
| stored an "as-of" date, so that you could override or correct a
| value for a specific value date by specifying a newer "as-of"
| date).
|
| To query a value for a given date, you'd specify key and value
| date, and it would then give you the value with the latest "as-
| of" date. Almost always, there'd only be one "as-of" date, and
| rarely, when a correction had happened, there'd be two, extremely
| rarely three.
|
| Now, the SQL query then was some sort of group_by and join where
| as_of = max over the group of as_of. Conceptually, just find the
| key and date, then pick the last as_of (if there are more than
| one). But the stupid engine did this massive self-join with a
| hash-join, and it was terribly slow. The conceptually simple idea
| turned out to be a disaster.
|
| Made me realise
|
| a) how little I know about DBs.
|
| b) that you can get a correct solution in a language by
| understanding the language a bit; but to obtain an efficient
| solution, you need to understand the language rather well.
|
| (Note: the SO question below is just about this topic, and I
| assume (hope) there are performant solutions to this nowadays. (I
| worked on this back when Microsoft SQL Server had just introduced
| an XML datatype for structured unstructured columns...)
|
| https://stackoverflow.com/questions/121387/fetch-the-row-whi... )
| derekperkins wrote:
| We tried the same thing a few years ago in MySQL that was
| randomly non-performant: 4000x slower. Still waiting for that
| to be fixed.
| mst wrote:
| I confess that 'type' sounds to me like the sort of thing that's
| going to appear in a WHERE clause often enough that I might as
| well make an enum for it and materialise that out on insert
| whether by trigger or otherwise.
|
| Neat trick for the meantime though, I'm sure making a schema
| change like the one I'm thinking of would be a colossal pain in
| the buttocks if decided late in the run up to shipping a feature.
| fabian2k wrote:
| I'm a huge, huge fan of JSONB columns in Postgres, but I'd
| agree here. Anything that is consistent and important enough
| should be pulled out of JSON and put into a real column.
| Postgres has some impressive functionality to query JSON, but
| it's not the same as with real columns and you run into
| performance issues much, much quicker.
| asah wrote:
| 2c: in most cases, a Postgres index is (performance wise)
| identical to a "real column" just without the name, and
| indeed you have more control over the data structure for an
| index, and indexes can include lossy compression (e.g.
| partial indexes) unlike a real column...
|
| (obviously, the challenge is getting pg to use your index for
| a given query... for that, I like to use VIEWs to help query
| authors write queries that use the indexes)
| kmdupree wrote:
| You're exactly right that our schema could be better and that
| it's non-trivial to execute on a schema change, especially
| because we actually run a distributed Postgres cluster via
| Citus AND we use a special sharding method that we manage
| manually.
|
| We actually just started working towards how we might do a
| schema change for the 1+ million shards in our cluster.
| Hopefully, we'll be able to write up some learnings on the
| schema change after its done. :)
| icsa wrote:
| I had a similar situation a few years back. Postgres has
| constructs that are optimization barriers. Once I understood this
| and reviewed the Postgres source code to determine the semantics
| of the relevant constructs, it was possible to get a 10X
| improvement for complex set of queries that had already been
| optimized for several years.
| albertopv wrote:
| I'm not a dba, and I admit having years of experience on Sql
| Server and only few recent months with postgres, and I know it's
| not a popular opinion, but so far, generally speaking, including
| admin stuff, I found Sql Server to be superior. It has its
| problems, of course, but with Sql Server I found better tooling
| and OOTB experience, easier maintenance, more features easier to
| use. E.g. do something like sp_help or sp_helptext exists in
| postgres? Real and simple case insensitive search, even just on a
| single comparison in a query? Real collations? Page and row data
| compression (in Sql Server you can obtain good perf improvement
| enabling compression thanks to fewer IO ops)? Column encryption?
| Query store, with the possibility to choose a plan? Graphical
| exec plan? A true sql language with, e.g., variable declaration
| in a statement (DO postgres is a hack, not a solution) outside of
| function or stores procedure? Manual Vacuum, really? Never heard
| of in sql server (shrink is different, vacuum equivalent is an
| automatic background job). Sql Server is quite predictable, or
| maybe it's me that I know it much more than postgres.
|
| However, there are many postgres feature really missing in Sql
| Server, or added only in recent versions (consider that many
| clients are still using Sql Server 2012, a few even 2008R2!),
| idempotent DDL is much easier on postgres than Sql Server older
| than v2016, json support is waaaay better, many more useful data
| type...
|
| I know nothing about replicas, clusters, failover etc, those have
| always been managed by proper DBAs.
| [deleted]
| valyagolev wrote:
| happened to us but for an even weirder case. postgres wasn't
| doing a simple index-only scan because, as i learned from
| googling, the row of the table wasn't that much wider than the
| index. creating a huge extra column helped
|
| i wish there was no fancy query planner at all, and definitely no
| obligatory ones. it's 2021, we either learn how to plan
| computation, or can afford not to care
| hobs wrote:
| There's so many cases where this is the wrong opinion that it
| is mind boggling to see.
|
| There's so many queries NOT blogged about that just do the
| thing you want pretty much all the time, for no extra work on
| your part.
| valyagolev wrote:
| query planner is a very complex, unpredictable piece of
| software that makes decisions I end up paying for. yes, I
| want it dismantled, and the same problem addressed somehow
| otherwise. maybe via dumber "query planners" and better
| "hints", even though the word "hint" doesn't go far enough in
| the direction of control that I need. I won't be sorry about
| this opinion even if you're used to this state of affairs
|
| (i edited my comment very slightly to avoid getting more
| responses like yours)
| cosmotic wrote:
| Using SQL workarounds/hacks just makes the code harder to read
| and harder for the query optimizer to detect the original
| intention and optimize in the future.
| talos wrote:
| This is true, but the workaround proposed in the article is not
| a change to the query at all. It's adding another index, which
| is a pretty classic case of "proper" usage of query analysis
| (as opposed to confusing/hacky mutations of the query itself).
| shock-value wrote:
| Unless I'm misreading they didn't add another index. They
| actually just reduced the original index by removing an
| indexed expression and excluding rows that didn't match that
| expression instead.
| jeffbee wrote:
| You should be able to dictate the exact query plan to every
| database. SQL is a bad and obsolete API.
| jve wrote:
| I have an experience with MS SQL Server, so can only comment
| about that. And regarding performance, mainly with old 2008R2
| version.
|
| The query planner isn't very clever at times. "At times" actually
| makes it worse, because performance bugs surface sometimes.
|
| Anyways, the result is multiple orders of magnitude difference.
| What takes 0.3-2ms suddenly takes hundreds of milliseconds or
| even seconds to complete. Multiply that with millions of
| execution count and there is a problem.
|
| Sometimes SQL Server WILL NOT CHOOSE covering index (with many
| include columns), because it also evaluates index size. And if
| SQL thinks that some seek on clustering index is specific enough
| for parameter value A, then it is disaster with parameter B.
|
| Good thing SQL server features plan guides, where you can tell
| server which index to use or provide other hints. Saves the world
| when dealing with 3rd party applications.
|
| The stuff you have to do deal with your db grows as row count
| grows. If configured well, can support billions of rows, as we
| see from this post.
| shock-value wrote:
| > Good thing SQL server features plan guides
|
| If only Postgres maintainers got this message.
| protomyth wrote:
| I remember working on a Sybase 11.5 database that basically
| required forcing of indexes for many tables because the query
| planner would always go off the rails. We had to run a script
| in production that would print out the query plan of the
| currently active queries that were taking forever. Without
| being able to force indexes, I have no idea how we'd of fixed
| the problem.
| shock-value wrote:
| I like Postgres but this aspect is one of its shortcomings.
| In Postgres you usually have to rework your query in weird
| ways and hope that it's possible to get the optimizer to
| make the right choice. Even then, due to its usage of
| statistics, it could switch plans at any time as your data
| grows/changes.
| azth wrote:
| Looks like we could use a "hint" ;-)
| protomyth wrote:
| The worst part is database performance goes from great to sucky
| with no in between. Once a query's plan make it fall off a
| cliff, you really need the hints (forced indexes) that SQL
| Server provides to get the plan back to acceptable. Even
| updating the statistics tends not to fix the problem.
| trhway wrote:
| They added an index to a table getting very active flow of
| writes. It would be very interesting to see the write performance
| change too, so the picture would be more balanced.
| bluedino wrote:
| In articles like these, I wish they would go a little deeper and
| says _why_ the system does what it does. Perhaps the default
| behavior is geared towards low-memory servers, or avoids disk
| writes, or the data is usually expected to be of a certain type.
|
| Perhaps the system "isn't very smart" like it says, or it was
| originally intended for other purposes.
| fabian2k wrote:
| I don't know the internals here, but I think I can make a
| plausible guess here. The index-only scan is generally
| triggered only when every column in your query is contained in
| the relevant index. In this case the index is a functional
| index, so it's not only plain columns in there. The function
| needs the data column, but the functional index already stores
| the result of the function. To the query planner it looks like
| the query needs the data column, while it actually doesn't need
| it.
|
| This is a bit of an uncommon case that would need special
| handling in the query planner. So it's not that odd that it is
| not implemented yet, though I would suspect that it'll be added
| at some point.
| talos wrote:
| I definitely agree with you, but in this case the Postgres team
| itself frames the suboptimal behavior as such:
|
| > However, PostgreSQL's planner is currently not very smart
| about such cases.
|
| (From https://www.postgresql.org/docs/10/indexes-index-only-
| scans....)
|
| I suspect this is just a rough edge. Many types of functions
| may not be consistent, and therefore would need to be re-
| executed on the original data. Perhaps some of the planner
| functionality here predates function notation like `IMMUTABLE`.
| orthoxerox wrote:
| And yet Postgres still refuses to add hints.
|
| I'd rather deal with a nonexistent query optimizer, like the one
| in Clickhouse, than with an insufficiently smart one that I can't
| control.
| mike_hock wrote:
| A hint won't make the query planner use an index column it
| doesn't know it can use, though.
| lazide wrote:
| It does tell the query planner it can use it though.
| SpicyLemonZest wrote:
| "Doesn't know it can use" in this context means the system
| probably doesn't know _how_ to use it, even if a hint
| suggests it should.
| [deleted]
| jeff-davis wrote:
| Right, hints can only tell the database to use features it
| already has, they don't add new features on the fly.
|
| I think the hints discussion is interesting, but not relevant
| to this particular article.
| fennecfoxen wrote:
| Just about when I was leaving [financial-service company] our
| team was considering that the Postgres query planner was a
| significant source of risk, and contemplating mitigations and
| alternatives.
|
| We'd optimize things so that on our busy day each month, query
| A would take 2 minutes, and query-set B would take 30 or so in
| total, and we'd have nice graphs to track trends over time. But
| every so often, the query planner would change its mind about
| how to do some part of the operation. You'd be looking at query
| A suddenly taking 2+ hours, while query-set B hadn't even
| started yet. In the worst cases, someone would have to get on
| the phone with our banking partner, and ask for an extended
| deadline tonight.
|
| Business-critical? The job in question _was_ the business,
| literally the operation customers were paying for (in
| combination with a quick template-fill and SFTP, anyway).
|
| It was particularly hard to nail down because the plans would
| depend on the specific customers in question, and the
| transactions they were doing that day.
| baq wrote:
| we're running 'ANALYZE table' with default_statistics_target
| set in the connection to the max reasonable value for the
| dataset size every 4 hours to avoid just that. also, the
| default checklist item for sudden slowness of postgres is a
| manual ANALYZE with a bumped statistics target.
| jandrewrogers wrote:
| Yes, the Postgres query planner can break in ways that are
| operationally catastrophic in large systems. Many companies
| have experienced this.
|
| The root cause is that the statistics collection architecture
| of Postgres that feeds the query optimizer has some deep
| design flaws. Under some conditions it will produce
| statistical models of the data that are badly skewed in
| random ways, which causes the query optimizer which uses
| those statistics to make erratic and incorrect decisions.
|
| Fixing the statistics collection would be a massive
| undertaking, the issue is architectural in nature. Adding
| hints would provide a reasonable workaround and is probably
| easier in terms of development complexity than fixing the
| statistics collection.
|
| It is possible to reduce the probability of these bugs by
| reorganizing the data. The idea of changing your data model
| to work around a database bug is pretty horrific but
| companies do it.
| atombender wrote:
| Presumably some commercial databases like MS SQL Server and
| Oracle are better at this? I'd love to know how, and in
| what way the Postgres architecture is inferior.
| shock-value wrote:
| It's so odd because Postgres generally does everything else
| so well and is generally very customizable and modular
| otherwise. Yet the maintainers totally scoff at the idea of
| query hints even in the face of so many examples of planning
| failures.
|
| Their arguments are asinine too (from
| https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion):
|
| _* Poor application code maintainability: hints in queries
| require massive refactoring._
|
| _* Interference with upgrades: today 's helpful hints become
| anti-performance after an upgrade._
|
| _* Encouraging bad DBA habits slap a hint on instead of
| figuring out the real issue._
|
| _* Does not scale with data size: the hint that 's right
| when a table is small is likely to be wrong when it gets
| larger._
|
| _* Failure to actually improve query performance: most of
| the time, the optimizer is actually right._
|
| _* Interfering with improving the query planner: people who
| use hints seldom report the query problem to the project._
|
| Literally all of these are expressions of condescension and
| distrust of their own users. Anyone who would use hinting
| would be using it to solve a real issue caused by the
| shortcomings of the planner -- that much should be self-
| evident.
| rrrrrrrrrrrryan wrote:
| Is it possible to at least lock-in a query plan with
| Postgres?
|
| I understand the philosophy behind wanting to keep the
| queries themselves as declarative as possible, but there
| should be _some_ way to prevent a really important query
| from randomly going rogue and using a stupid index, just
| because something completely unrelated changed somewhere
| else in the database.
| jandrewrogers wrote:
| Ironically, the underlying cause of the query planner
| failures is a defect in the statistics collection
| architecture -- it doesn't scale to large databases in some
| cases. An argument of "does not scale with data size"
| applies to the query planner.
| zepearl wrote:
| I agree - it's probably not nice/elegant/whatever to use
| hints, but my opinion is that a query planner cannot probably
| cover 100% of all possible combinations of
| layouts/queries/data => not having the possibility to use
| hints is for me just a huge risk.
|
| Additionally in PROD environments you have the "time"-factor
| about fixing something; often you cannot afford a week of
| deep analysis trying to understand why the planner does not
| work as expected respectively what changed about the data
| that made it think in a different way etc... .
|
| Having said this, I just installed PostgreSQL last week,
| hehe. I did it only after somebody on HN mentioned the
| extension "pg_hint_plan" (
| https://pghintplan.osdn.jp/pg_hint_plan.html +
| https://pghintplan.osdn.jp/hint_list.html ) => downloaded &
| built & installed it on Debian 10 for PostgreSQL 13 => it
| seems to work (but so far I did only few initial tests). But
| it's still a bit a risk as it's an external dependency.
| bmurphy1976 wrote:
| I haven't done much database work in recent years, but this was
| always one of my frustrations. I'd take predictability over max
| performance any day.
___________________________________________________________________
(page generated 2021-08-02 23:01 UTC)