Re: Indexes on expressions with multiple columns and operators

=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic(dot)yhuel(at)dalibo(dot)com> writes:
> Hello, in the following, I don't understand why:
> 1) the expression index isn't used in the first EXPLAIN

The planner doesn't look for multi-clause matches of that sort.
You could apply a little ju-jitsu perhaps:

regression=# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE (ackid IS NULL AND crit = 'WARNING') is true;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using foo_expr_idx on foo (cost=0.29..8.39 rows=5 width=17) (actual time=0.013..0.016 rows=5.00 loops=1)
Index Cond: (((ackid IS NULL) AND (crit = 'WARNING'::text)) = true)
Index Searches: 1
(3 rows)

but my own tendency would be to use a partial index rather than a
boolean-valued index:

regression=# CREATE INDEX foo_partial_idx ON foo (id) WHERE ackid IS NULL AND crit = 'WARNING';
CREATE INDEX
regression=# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE ackid IS NULL AND crit = 'WARNING';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using foo_partial_idx on foo (cost=0.13..107.18 rows=990 width=17) (actual time=0.010..0.014 rows=5.00 loops=1)
Index Searches: 1
(2 rows)

The advantage of a partial index is you might be able to have the
index entries themselves carry some other column(s), allowing
more queries to be made into index-only scans. I put "id" here,
which might or might not be of any use in this specific toy example.

> 2) the number of estimated rows is completely off in the second EXPLAIN,
> whereas the planner could easily use the statistics of foo_f_idx.

Hmm, not sure about that. Again, boolean-valued indexes aren't
something we've worked on too hard, but I don't see why that
would affect this case.

regards, tom lane