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?
Date: Thu, 21 Mar 2019 03:46:58 +0000
Message-ID: <87tvfwx5x0.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net>
References: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net>
>>>>> "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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@tao11.riddles.org.uk, raj@gusw.net
Subject: Re: Poor man's partitioned index .... not being used?
In-Reply-To: <87tvfwx5x0.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox