3 ms·

I would say that Postgres' cost model is quite sophisticated, and not at all primitive. Some weigh-able costs like toast size might indeed be missing, but I wou

I would say that Postgres' cost model is quite sophisticated, and not at all primitive. Some weigh-able costs like toast size might indeed be missing, but I would not dare to argue that it is primitive because of the lack of taking toast size into account; indeed I would argue that you missing this is showing how many measured points already are available. Regarding missing join statistics: What exactly do you mean by that? You can already add multi-column statistics, which would allow postgres to optimize on correllations that cross column boundaries (it can use statistics on [id, value] to estimate the occurance of column value when joined on id). But, you must specify the existence of cross-column corellated statistics manually, because all statistics slow down the planner by a bit, because it has to consider the statistic.

darksaints 6y ago
> I would say that Postgres' cost model is quite sophisticated, and not at all primitive. Some weigh-able costs like toast size might indeed be missing, but I would not dare to argue that it is primitive because of the lack of taking toast size into account; indeed I would argue that you missing this is showing how many measured points already are available. They aren't measured though, they're configured. And poorly-defaulted or misconfigured costs are a major reason for bad plans. They could be measured though, and that would be some low hanging fruit for substantial improvements to the cost model. Furthermore, they seem to have no effect on filtering predicate evaluation order...even when selectivity of one predicate is highly predictive, and the other predicate is extremely costly to compute. A bigger problem is that inaccuracies compound when more complex plans unfold. A small inaccuracy in the rowcount estimate from one set of rows, combined with a small inaccuracy from a rowcount estimate for a different set of rows, joined together, can result in cost estimates that are orders of magnitude off from reality. > You can already add multi-column statistics, which would allow postgres to optimize on correllations that cross column boundaries Multi-column statistics do help, but only with very simplistic scenarios. You can only collect cross-column statistics within a single table. You can't collect statistics across foreign-keyed tables, commonly joined column/tables, nor are they helpful for anything but pure equality comparisons. Geospatial relationships, range comparisons, inequalities, etc.., they all get no help from multi-column statistics, unless you're going to collect them across table relationships.

darksaints 6y ago

> I would say that Postgres' cost model is quite sophisticated, and not at all primitive. Some weigh-able costs like toast size might indeed be missing, but I would not dare to argue that it is primitive because of the lack of taking toast size into account; indeed I would argue that you missing this is showing how many measured points already are available. They aren't measured though, they're configured. And poorly-defaulted or misconfigured costs are a major reason for bad plans. They could be measured though, and that would be some low hanging fruit for substantial improvements to the cost model. Furthermore, they seem to have no effect on filtering predicate evaluation order...even when selectivity of one predicate is highly predictive, and the other predicate is extremely costly to compute. A bigger problem is that inaccuracies compound when more complex plans unfold. A small inaccuracy in the rowcount estimate from one set of rows, combined with a small inaccuracy from a rowcount estimate for a different set of rows, joined together, can result in cost estimates that are orders of magnitude off from reality. > You can already add multi-column statistics, which would allow postgres to optimize on correllations that cross column boundaries Multi-column statistics do help, but only with very simplistic scenarios. You can only collect cross-column statistics within a single table. You can't collect statistics across foreign-keyed tables, commonly joined column/tables, nor are they helpful for anything but pure equality comparisons. Geospatial relationships, range comparisons, inequalities, etc.., they all get no help from multi-column statistics, unless you're going to collect them across table relationships.