Working around a case where the Postgres planner is “not very smart”

https://heap.io/blog/when-the-postgres-planner-is-not-very-smart

by kmdupree • 5 years ago

65 58 5 years ago

fabian2k3• 5 years ago

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.

kmdupree2• 5 years ago

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.

mst2• 5 years ago

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.

fabian2k3• 5 years ago

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.

asah2• 5 years ago

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)

kmdupree2• 5 years ago

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. :)

mst2• 5 years ago

Right, your current solution is what I'd classify as an "expedient field hack" if I were the one implementing it, and I do mean that positively :D

I look forwards to comparing notes when that post comes out.

jve• 5 years ago

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-value4• 5 years ago

Good thing SQL server features plan guides

If only Postgres maintainers got this message.

protomyth2• 5 years ago

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.

azth• 5 years ago

Looks like we could use a "hint" ;-)

asah2• 5 years ago

FYI one method is to use CTEs (WITH) to force Postgres via materialization.

https://www.postgresql.org/docs/current/queries-with.html

protomyth2• 5 years ago

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.

bluedino• 5 years ago

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.

fabian2k3• 5 years ago

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.

talos2• 5 years ago

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`.

orthoxerox2• 5 years ago

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.

bmurphy1976• 5 years ago

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.

mike_hock2• 5 years ago

A hint won't make the query planner use an index column it doesn't know it can use, though.

lazide2• 5 years ago

It does tell the query planner it can use it though.

jeff-davis• 5 years ago

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• 5 years ago

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• 5 years ago

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.

shock-value4• 5 years ago

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.

zepearl• 5 years ago

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.

jandrewrogers4• 5 years ago

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.

cosmotic• 5 years ago

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.

talos2• 5 years ago

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-value4• 5 years ago

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.

valyagolev2• 5 years ago

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• 5 years ago

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.

valyagolev2• 5 years ago

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)

jeffbee• 5 years ago

You should be able to dictate the exact query plan to every database. SQL is a bad and obsolete API.

infogulch• 5 years ago

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.

albertopv• 5 years ago

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.

trhway• 5 years ago

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.

icsa• 5 years ago

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.

FabHK• 5 years ago

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• 5 years ago

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.

barrkel• 5 years ago

Window functions can help here. You can try a self-join with MySQL (and it won't do a hash join because it doesn't have it; it didn't have hash joins at all before 8.10 and it still doesn't have hash outer joins, and what you want to eliminate the older rows is an anti-join, i.e. asserting null on a left join) but honestly you're better off sorting by timestamp and using a manual window when processing the results.

andreareina2• 5 years ago

Standard sql:

    select key, value, asof
    from (
      select *, row_number() over (partition by key order by asof desc) as rn
      from <table>
    ) t
    where rn = 1

postgresql:

    select distinct on (key)
    key, value, asof
    from <table>
    order by key, asof desc

The DISTINCT ON clause takes the first row that the query returns, which the ORDER BY makes sure is the latest value

Disclaimer: I've used both methods, but haven't tested the perf against each other or the self-join.

Inviz2• 5 years ago

Timescaledb adds a custom scan for speeding up distinct on out of the box. Just install extension, no extra work is needed.

https://blog.timescale.com/blog/how-we-made-distinct-queries...

I tried it and it really is that fast.

BitPirate• 5 years ago

The other thing i'm currently struggling with is the slow parameter sniffing in SQL server. We would really like to switch to the official mssql-jdbc driver but the performance is abysmal:

https://github.com/microsoft/mssql-jdbc/issues/1196#issuecom...

Working around a case where the Postgres planner is “not very smart”

Related Stories