Jonathan Chen <jonc@xxxxxxxxxxx> 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
