Radoslav Nedyalkov <rnedyalkov@xxxxxxxxx> 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
