Jeff Janes <jeff.janes@gmail.com> writes:
> Why does having the =ANY in the "Index Cond:" rather than the "Filter:"
> inhibit it from understanding that the rows will still be delivered in
> order by "thousand"?
They won't be. The =ANY in index conditions results in multiple
index scans, that is we effectively do a scan with
Index Cond: (thousand < 2) AND (tenthous = 1001)
and then another with
Index Cond: (thousand < 2) AND (tenthous = 3000)
and only by very good luck would the overall result be sorted by
"thousand". On the other hand, if the ScalarArrayOp is a plain
filter condition, then it doesn't affect the number of index
scans --- it's just a (rather expensive) filter condition.
indxpath.c's get_index_paths() is explicitly aware of these
considerations, maybe reading the comments there would help.
I don't say there couldn't be a bug here, but you haven't
demonstrated one. I believe that get_index_paths() will
generate paths both ways, with the ScalarArrayOp as a filter
condition and an indexqual, and it's evidently deciding the
first way is cheaper.
regards, tom lane