>>>>> "Gunther" == Gunther <raj@gusw.net> writes:
Gunther> foo=# CREATE UNIQUE INDEX Test_pk0 ON Test(id) WHERE mod(id,2) = 0;
Gunther> CREATE INDEX
Gunther> foo=# EXPLAIN SELECT * FROM Test WHERE id = '8934';
Gunther> QUERY PLAN
Gunther> -------------------------------------------------------
Gunther> Seq Scan on test (cost=0.00..6569.61 rows=1 width=4)
Gunther> Filter: (id = 8934)
Gunther> (2 rows)
Gunther> Why is that index never used?
Because the expression mod(id,2) does not appear in the query, and there
is no logic in the implication prover to prove that (mod(id,2) = 0) is
implied by (id = 8934).
If you did WHERE mod(id,2) = mod(8934,2) AND id = 8934
then the index would likely be used - because the prover can then treat
mod(id,2) as an atom (call it X), constant-fold mod(8934,2) to 0 because
mod() is immutable, and then observe that (X = 0) proves that (X = 0).
Pretty much the only simple implications that the prover can currently
deduce are:
- identical immutable subexpressions are equivalent
- strict operator expressions imply scalar IS NOT NULL
- (A op1 B) implies (B op2 A) if op2 is op1's declared commutator
- Btree semantics: if <, <=, =, >=, > are all members of a btree
opfamily, and <> is the declared negator of =, then implications
like (X < A) and (A <= B) implies (X < B) can be deduced.
--
Andrew (irc:RhodiumToad)
