[btown]

What about when column statistics don’t accurately describe a subset of the table that you know from a business perspective is likely to have different dynamics? Allowing the client to essentially A/B test between different advisements and evaluate performance, with full knowledge of that business context, may be meaningful at certain scales.

[phamilton]

That's valuable, but cranking statistics up to 10000 and/or creating custom statistics can go a very long way to helping the planner understand the dataset more fully.

[justinclift]

Sure, but for production environments being able to tell the planner to do it the way you want (aka "fix it for right now") is often the overriding priority.

The "make it work work perfectly from just statistics" can come later, when time is more flexible. Probably on an identical non-production copy of the environment, if that shows the same performance characteristics. ;)

[heavenlyblue]

Can you please explain how cranking statistics 10000 and/or creating custom statistics is a more straightfoward way to enforce a certain query plan VS telling the databsee which plan should be used instead?

[phamilton]

The goal isn't to enforce a certain query plan. The goal is to execute queries efficiently.

The planner/optimizer is good at its job. In general, better than I am. If I can inform it about a particular shape of data, all queries around that data will improve. If I tell it exactly how to execute one query, only that query improves.

Additionally, setting statistics to 10000 is really easy. Easier than tweaking a query. Creating custom statistics is more work and may or may not be worth exploring as a quick fix. But if you know the data is shaped irregularly, why wouldn't you make sure the planner knows about it?