On Thu, 21 Mar 2019 at 15:51, Gunther <raj@xxxxxxxx> wrote: > foo=# CREATE UNIQUE INDEX Test_pk0 ON Test(id) WHERE mod(id,2) = 0; > CREATE INDEX > foo=# CREATE UNIQUE INDEX Test_pk1 ON Test(id) WHERE mod(id,2) = 1; > CREATE INDEX > foo=# ANALYZE Test; > ANALYZE > foo=# EXPLAIN SELECT * FROM Test WHERE id = '8934'; > QUERY PLAN > ------------------------------------------------------- > Seq Scan on test (cost=0.00..6569.61 rows=1 width=4) > Filter: (id = 8934) > (2 rows) > > Why is that index never used? When the planner looks at partial indexes to see if they'll suit the scan, the code that does the matching (predicate_implied_by()) simply does not go to that much trouble to determine if it matches. If you look at operator_predicate_proof() you'll see it requires the expression on at least one side of the OpExpr to match your predicate. Yours matches on neither side since "id" is wrapped up in a mod() function call. Certainly, predicate_implied_by() is by no means finished, new smarts have been added to it over the years to allow it to prove more cases, but each time something is added we still need to carefully weigh up the additional overhead of the new code vs. possible benefits. It may be possible to do something with immutable functions found in the expr but someone doing so might have a hard time proving that it's always safe to do so. For example, arg 2 of your mod() call is a Const. If it had been another Var then it wouldn't be safe to use. What other unsafe cases are there? Is there a way we can always identify unsafe cases during planning? ... are the sorts of questions someone implementing this would be faced with. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
