Re: Poor man's partitioned index .... not being used?

>>>>> "Gunther" == Gunther <raj(at)gusw(dot)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)