Debugging the Postgres query planner (2018)
In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate:  Planners are too smart for their own good.<p>This is a standard story: A query ran for a long time without issue, and then, one day, some minor shift in your statistics happens, and now you have a major performance issue in your hands, without any real change on prod.  No non-productive environment can help you:  They don&#x27;t have the same data or they do have the same data but the statistic sampling was slightly different.<p>If it happens, you have to find an incantation that makes the planner comprehend what&#x27;s going wrong.  Postgresql has things like the statistic object and in this case the statistic column property, but finding the right incantation can be black magic, and adding indexes&#x2F;statistics&#x2F;... can take hours so trial and error is slow.<p>Dumber databases have an edge here: Their performance is probably lower, but it is predictable.<p>Some RDBMS have mitigations, e.g. Oracle&#x27;s optimizer plan stability allows you to make the plan unchangeable.  It&#x27;s a 2-sided knife of course: It won&#x27;t get better if the data has a chance for it, but it won&#x27;t get worse either.