From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Gunther <raj@gusw.net>
Cc: "pgsql-performance." <pgsql-performance@postgresql.org>
Subject: Re: Poor man's partitioned index .... not being used?
In-Reply-To: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net> (Gunther's
	message of "Wed, 20 Mar 2019 22:45:07 -0400")
Message-ID: <87tvfwx5x0.fsf@news-spur.riddles.org.uk>
References: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Thu, 21 Mar 2019 03:46:58 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "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)