Re: When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)

Timothy Garnett <tgarnett@panjiva.com> writes:
> ... My first thought was that there was a problem with the
> statistics/estimation in the planner, but using "set enable seq_scan=off;"
> still does not use the index when there's over 100 bid's in the IN clause.
> Breaking the IN clause into 2 < 100 element groups does however rescue the
> use of the index and the fast performance as does creating a new non-partial
> index on bid (i.e. an index "index_scm_on_bid2" btree (bid) WITH
> (fillfactor=100) will be used with over 100 bid's).

I think you're hitting the code that abandons attempts to prove
constraints true when the expressions get too large (to avoid O(N^2)
or worse behavior).  Could you just add an explicit AND bid IS NOT NULL
when you know none of the items in the IN clause will be null?

            regards, tom lane