Re: single table - fighting a seq scan

Radoslav Nedyalkov <rnedyalkov(at)gmail(dot)com> writes:
> Shame on me. It's a partial index - *where is not null.*
> Put the* is not null *predicate in place and planner always goes for index.
> (tested with thousands of IN entries)
> CTE version always goes for index too even *without **is not null , *which
> led to a slight confusion.

Ah. That's actually something we fixed in v12 (see [1]). In the CTE
version, the planner can prove "x is not null" from "x = cte_value" even
without knowing what the CTE output value is, just on the basis that "="
is strict. In the IN form, it's likewise possible to prove "x is not
null" from "x IN (list)", but you need a special test to recognize that.
With a short IN list, the planner converts IN to "x = this OR x = that
OR x = the-other ..." and can make the proof from that formulation.
But we prevent it from trying that on long IN lists, because it'd eat
lots of cycles and perhaps not be able to prove the desired partial index
qual anyway.

regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=65ce07e02