[londons_explore]

I don't like the way postgres doesn't have the ability to switch query plans mid query.

Frequently a query plan turns out to perform nowhere near as well as the planner expects (for example, because the data distribution is poor, or a key being filtered for doesn't exist). In those cases, flipping the query plan around could turn a 1 hour query into a 1 millisecond query. Yet postgres doesn't have the ability to do that.

Sure a human can sometimes use domain knowledge to sometimes be able to rearrange the query to force a plan that works well, but in the general case, the database shouldn't have performance drop by a factor of 1 million because of semi-arbitrary planner decisions. Being able to try multiple possible plans would be a massive start in solving the issue.

[londons_explore]

For anyone interested in implementing this, here is what I found last time...

Postgres queries are streamed to the client - ie. some results are delivered before the query is done running. That functionality is necessary for really big resultsets.

That makes it difficult to change plans mid query, because your new plan might return results in a different order, and you need to filter any already-returned results, but you can't afford to keep all of the already-returned results in RAM. Even if that weren't an issue, I'm not even sure that it's always valid to do this.

To add even more complexity... The postgres protocol allows the client to reverse the query (ie. midway through getting the results, the client can say "yo, go back, and return results from earlier again"). It must return the same results in reverse order. That means if you do switch query plans, when the client goes backwards, the server needs to un-switch query plans back to the old plan when going backwards.

These issues are not insurmountable... But they certainly stopped me implementing it in the day I had set aside for the task...

[btown]

I would love to be able to SET multiple_planning; on a cursor to be able to say “yes I know that reversing would be UB, and I have set a limit so I don’t care if results aren’t streamed, so please just try everything you can.” In general I wish there were better ways to tell these systems you know what you’re doing.

[cormacrelf]

There could also be a “clean the slate” streaming directive, which means “I started the query again, please scratch what got delivered already”.

[JelteF]

That effictively means the client cannot actually start processing the results before everything is received. Because you would have to undo everything you did with the data you received before. So basically this would simply be a non streaming version.

[Bootvis]

I believe the best you can do is to delay streaming. Now you have the problem of deciding when to start...

[ako]

The decisions a database makes aren't semi-arbitrarily: they are completely based on your statistics. Usually wrong decisions are caused by outdated statistics.

[londons_explore]

The statistics cant accurately represent most data distributions, and errors stack multiplicativly with deep query plans. Typical query plans, even with fresh statistics, are lucky to be within a factor of 10, or even 100 of the true cost of running the query.

[antpls]

I would be interested to have some academic pointers or actual benchmarks about that claim

[londons_explore]

Unfortunately the benchmarks I ran to get that knowledge aren't public. They were run on a ~1TB database of the production data of a mid size web company. They spanned thousands of different queries. They were trying to answer the question "is our business better off to have out of date statistics or fresh statistics", and out conclusion was that fresh statistics were too risky because they can cause a query plan to change unexpectedly in the middle of the night, and the whole database to fall over from resource exhaustion as some common query now takes hours to complete. The companies most costly downtime had already been caused by that once.

We instead generated statistics on a backup copy, then loadtested that the statistics worked acceptably, and then wrote those statistics to the production database. We did that every 3 months, because we found that outdated statistics didn't really have any appreciable performance impact, and performance degradation was gradual rather than a cliff.