On Thu, 31 Jul 2025 at 08:14, Peter Geoghegan <pg@bowt.ie> wrote:
> I suggest that you rewrite affected queries to make them join against
> a VALUES() with the same constants as those currently used in the
> larger IN() list. If you're not sure whether the set of constants from
> the application will be reliably unique, you can use DISTINCT to make
> sure.
Even just presorting the IN list constants would make it better. If I
manually adjust the recreator query to sort the items in both IN
lists, I get:
Execution Time: 263.365 ms
vs:
Execution Time: 804.377 ms
Of course, the qsort_arg() call still happens, it'll hit qsort_arg's
presorted short-circuit case and will do very little.
I've not looked in great detail, but I did wonder if it's worth
adjusting ExecIndexBuildScanKeys() to sort the array in a
ScalarArrayOpExpr when it's Const beforehand. That might be a bit of
wasted effort if there's just one scan, however.
David