Re: num_sa_scans in genericcostestimate

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