Jonathan Chen <jonc@chen.org.nz> writes:
> I am running Postgresql 13 as a backend for Odoo, and I believe I have
> discovered a regression with the query engine.
As Adrian noted, you haven't provided enough detail to let anyone
offer more than generalities. However, in the spirit of generalities ...
ORDER BY with a small LIMIT is often a hard problem for the planner.
It has to guess whether a "fast start" plan is likely to win out over
a more straightforward plan. "Fast start" typically looks like "scan
from the end of an index on the ORDER BY column, immediately emitting
any row that satisfies the WHERE conditions, and stop when you have
enough". The other way generally involves collecting all the rows
satisfying WHERE, sorting them, and emitting the first few sort
outputs. For small LIMIT, the "fast start" way can win big, by
not having to visit most of the rows nominally satisfying WHERE.
It can also lose big, if most of the rows near the end of the index
turn out not to satisfy the WHERE, so that it has to keep on scanning.
With the limited facts you've offered, it appears that the v12 planner
chose the right way and v13 didn't, but we can't tell which way is the
one that was faster. It's unlikely that this is an actual regression,
in the sense of being anything we could fix. It's at least as likely
that v12 was just lucky, or was right for the wrong reasons. With an
example like you have here, with hard-to-estimate WHERE conditions,
there's a lot of luck involved ... especially if the WHERE conditions
are at all correlated with the ORDER BY order. In this case, with
an "IN sub-SELECT" condition that looks suspiciously correlated with
the outer join condition, it's quite likely that the planner is
arriving at a totally off-base guess about how many rows the IN
eliminates.
Rather than blaming the planner for not being lucky, you should be
thinking about what you can do to help it out. Can you get rid of
the additional join to account_move? (Note that the one I'm
suspecting is extra is the LEFT JOIN, not the IN.) I'd also
counsel losing the ORDER BY in the sub-select, as that's either
going to be ignored altogether or force a probably-useless sort,
not to mention creating an optimization barrier.
regards, tom lane