Re: Partial hash index is not used for implied qual.

I wrote:
> Wouldn't it be better to handle it more like the is_target_rel logic
> a few lines further up?

Actually, after thinking a bit longer, it'd be better to do something
like the attached so that we don't keep redundant quals unless they'd
*all* be excluded.

There's definitely something fishy about the costing though.
I experimented with this variant of Sergei's example:

regression=# CREATE TABLE hash_partial(x) AS SELECT x % 100 as y from generate_series(1, 1000) as x;
SELECT 1000
regression=# ANALYZE hash_partial;
ANALYZE
regression=# CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
CREATE INDEX
regression=# set enable_seqscan TO 0;  -- else we'll go for a seqscan
SET
regression=# EXPLAIN SELECT x FROM hash_partial WHERE x = 1;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on hash_partial  (cost=24.08..32.56 rows=10 width=4)
   Recheck Cond: (x = 1)
   ->  Bitmap Index Scan on partial_idx  (cost=0.00..24.07 rows=10 width=0)
         Index Cond: (x = 1)
(4 rows)

regression=# drop index partial_idx;
DROP INDEX
regression=# CREATE INDEX ON hash_partial USING hash(x);
CREATE INDEX
regression=# EXPLAIN SELECT x FROM hash_partial WHERE x = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on hash_partial  (cost=4.08..12.56 rows=10 width=4)
   Recheck Cond: (x = 1)
   ->  Bitmap Index Scan on hash_partial_x_idx  (cost=0.00..4.08 rows=10 width=0)
         Index Cond: (x = 1)
(4 rows)

Why are we thinking that a non-partial index would be substantially
cheaper to scan?  That seems surely wrong, and it runs counter to my
intuition about why this fix is incomplete.  (I expected an unfair
bias towards the partial index, not against it.)

            regards, tom lane

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c62e3f87724..c2687dec425 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -4038,6 +4038,7 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
     foreach(lc, rel->indexlist)
     {
         IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
+        List       *newrestrictinfo;
         ListCell   *lcr;

         if (index->indpred == NIL)
@@ -4051,8 +4052,8 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
         if (is_target_rel)
             continue;

-        /* Else compute indrestrictinfo as the non-implied quals */
-        index->indrestrictinfo = NIL;
+        /* Else compute new indrestrictinfo as the non-implied quals */
+        newrestrictinfo = NIL;
         foreach(lcr, rel->baserestrictinfo)
         {
             RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcr);
@@ -4061,8 +4062,18 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
             if (contain_mutable_functions((Node *) rinfo->clause) ||
                 !predicate_implied_by(list_make1(rinfo->clause),
                                       index->indpred, false))
-                index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);
+                newrestrictinfo = lappend(newrestrictinfo, rinfo);
         }
+
+        /*
+         * If we excluded every qual as implied by the predicate, and the
+         * index cannot do full-index scans, then it's better to leave
+         * indrestrictinfo alone so that we can still build a scan on this
+         * index.  XXX We will underestimate the cost of such an indexscan;
+         * what can we do about that?
+         */
+        if (!(newrestrictinfo == NIL && !index->amoptionalkey))
+            index->indrestrictinfo = newrestrictinfo;
     }
 }