Re: Performance regression: 9.2+ vs. ScalarArrayOpExpr vs. ORDER BY

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> commit 807a40c5 fixed a bug in handling of (new in 9.2) functionality
> of ScalarArrayOpExpr in btree index quals, forcing the results of
> scans including such a qual to be treated as unordered (because the
> order can in fact be wrong).
> However, this kills any chance of using the same index _without_ the
> SAOP to get the benefit of its ordering.

Hm, good point.

> I've experimented with the attached patch, which detects when this
> situation might have occurred and does another pass to try and build
> ordered scans without the SAOP condition. However, the results may not
> be quite ideal, because at least in some test queries (not all) the
> scan with the SAOP included in the indexquals is being costed higher
> than the same scan with the SAOP moved to a Filter, which seems
> unreasonable.

I'm not convinced that that's a-priori unreasonable, since the SAOP
will result in multiple index scans under the hood. Conceivably we
ought to try the path with and with SAOPs all the time.

regards, tom lane