I'll note that the clause is arbitrary in the sense that I don't generate it and cannot edit it but it's basically a bunch of boolean comparisons chained i.e. `col_a >= 1 and col_b <=5 and col_c ...` so I can in general add an index on say col_a and it does get used.
On Wed, Dec 18, 2024 at 9:47 PM Adrian Garcia Badaracco <adr...@adriangb.com> wrote: > I have a query where I have to run a where clause generated by another > system (i.e., I can't modify that where clause. The where clause may return > `null`, but I actually want to keep rows that return `null` (and rows that > return `true` but not rows that return `false`). > > I thought it would be as simple as wrapping in `(...) is not false` but > that seems to prevent index usage. > > For example, let's say that given the table: > > CREATE TABLE test_index (value INTEGER); > CREATE INDEX idx_value ON test_index(value); > > And the predicate `value = 5000`, if I run the original query that > excludes rows where the predicate is null it uses the index: > > SELECT * > FROM test_index > WHERE value = 5000; > > But as soon as I tack on an `IS NOT FALSE` the index is not used: > > SELECT * > FROM test_index > WHERE (value = 5000) IS NOT FALSE; > > This was surprising to me. I was hoping this might be able to use the > index. > > Is there any way to include the rows where the predicate evaluates to null > while still using an index? > >
