Re: IN list processing performance (yet again)

Dave Tenny <tenny(at)attbi(dot)com> writes:
> </blockquote>
> There is one very interesting thing in my test case though.&nbsp; It
> certainly <i>seemed</i> as if the<br>
> parameterized statements were successfully using the index of the
> freshly-created-but-unanalyzed table,<br>
> or else the times on those queries would have been terrible too.&nbsp; It
> was only the IN list form<br>
> of query that wasn't making correct use of the index.&nbsp; How can the
> planner recognize uniqueness for<br>
> one case but not the other?

The question is whether a seqscan will be faster than an indexscan; at
some point there's less I/O involved to just scan the table once. If
the planner doesn't know the index is unique then it's going to estimate
a higher cost for the indexscan (due to more rows fetched) and there is
some number of rows at which it will flip over to a seqscan. The same
will happen even if it *does* know the index is unique, it's just that
it will take more IN elements to make it happen. This is reasonable
behavior IMHO, although whether the flip-over point is anywhere near
the actual breakeven point on your hardware is anyone's guess. The cost
estimates are often far enough off that it's not very close.

regards, tom lane