3 ms·

A couple of things to point out: the JIT is very dependent on the Postgres cost model, which is still pretty primitive. For example, the cost model is not aware

A couple of things to point out: the JIT is very dependent on the Postgres cost model, which is still pretty primitive. For example, the cost model is not aware of TOAST sizes, which can drastically impact function execution costs if that function is size dependent (very common with things like geospatial queries). Another problem is the lack of join statistics...you can have perfectly recorded statics for both tables, but the combination of tables or columns used in the join, or possibly the expression used for the join, can cause cost estimate chaos. I know JIT plan caching is a hard problem, but it would be very helpful. But I actually think JIT is the wrong solution to this problem. Instead of recompiling an optimized plan every time, we should actually be using PGO. We already have a profiling mechanism built in: Explain Analyze. All that is needed is for that profiling mechanism to link up with the optimizer, giving it all the information it needs to do all of the same optimizations that we get with JIT today, but combined with extremely powerful global optimizations that no JIT would ever even attempt. And we already have the perfect use cases too: every view or materialized view is already based on a query that will be used regularly, and is worth profiling and optimizing.

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

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