Re: num_sa_scans in genericcostestimate

Jeff Janes <jeff(dot)janes(at)gmail(dot)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