Re: Function-based index not used in a simple query - Mailing list pgsql-sql

Rostislav Opocensky <orbis@pictus.org> writes:
> On Tue, 30 May 2000, Tom Lane wrote:
>> The problem here is that the optimizer will only consider an indexscan
>> for a clause that looks like index_key OP constant.  It doesn't think

> I'll consider having my index function return a `date'.  Still one thing
> remains unclear to me: why the optimizer doesn't use an indexscan in the
> stored procedure I have attached to my previous post.  The condition looks
> like WHERE trunc_to_day(timestamp) BETWEEN var1 AND var2.  var1 and var2
> get their values from calling the `volatile' function trunc_to_day, but
> from then on, their values can't be changed during the execution of the
> query.  Is it possible to give the optimizer a hint about it?

Hmm, actually the optimizer should/does regard those as constants within
subsequent queries (internally they are Params instead of Consts, but
that's supposed to be OK).  What I find here is that the optimizer does
consider an indexscan for this query, but there's a bug in its
selectivity estimation routine that causes it not to recognize the
BETWEEN clause as being a range restriction --- and that means it
produces a fairly high cost estimate for the indexscan.  I still got
an indexscan plan for a small test table, but on a larger table you
might not get one.

I've applied the attached patch for 7.0.1 --- if you are in a hurry,
you may care to apply it to your local copy.  It just tweaks the range-
query recognizer to accept Param as well as Const nodes.

            regards, tom lane


*** src/backend/optimizer/path/clausesel.c.orig    Tue May 30 00:26:44 2000
--- src/backend/optimizer/path/clausesel.c    Wed May 31 11:38:53 2000
***************
*** 120,129 ****
          Selectivity s2;

          /*
!          * See if it looks like a restriction clause with a constant. (If
!          * it's not a constant we can't really trust the selectivity!) NB:
!          * for consistency of results, this fragment of code had better
!          * match what clause_selectivity() would do.
           */
          if (varRelid != 0 || NumRelids(clause) == 1)
          {
--- 120,131 ----
          Selectivity s2;

          /*
!          * See if it looks like a restriction clause with a Const or Param
!          * on one side.  (Anything more complicated than that might not
!          * behave in the simple way we are expecting.)
!          *
!          * NB: for consistency of results, this fragment of code had better
!          * match what clause_selectivity() would do in the cases it handles.
           */
          if (varRelid != 0 || NumRelids(clause) == 1)
          {
***************
*** 134,174 ****

              get_relattval(clause, varRelid,
                            &relidx, &attno, &constval, &flag);
!             if (relidx != 0 && (flag & SEL_CONSTANT))
              {
                  /* if get_relattval succeeded, it must be an opclause */
!                 Oid            opno = ((Oper *) ((Expr *) clause)->oper)->opno;
!                 RegProcedure oprrest = get_oprrest(opno);

!                 if (!oprrest)
!                     s2 = (Selectivity) 0.5;
!                 else
!                     s2 = restriction_selectivity(oprrest, opno,
!                                                  getrelid(relidx,
!                                                           root->rtable),
!                                                  attno,
!                                                  constval, flag);
!
!                 /*
!                  * If we reach here, we have computed the same result that
!                  * clause_selectivity would, so we can just use s2 if it's
!                  * the wrong oprrest.  But if it's the right oprrest, add
!                  * the clause to rqlist for later processing.
!                  */
!                 switch (oprrest)
                  {
!                     case F_SCALARLTSEL:
!                         addRangeClause(&rqlist, clause, flag, true, s2);
!                         break;
!                     case F_SCALARGTSEL:
!                         addRangeClause(&rqlist, clause, flag, false, s2);
!                         break;
!                     default:
!                         /* Just merge the selectivity in generically */
!                         s1 = s1 * s2;
!                         break;
                  }
-                 continue;        /* drop to loop bottom */
              }
          }
          /* Not the right form, so treat it generically. */
--- 136,183 ----

              get_relattval(clause, varRelid,
                            &relidx, &attno, &constval, &flag);
!             if (relidx != 0)
              {
                  /* if get_relattval succeeded, it must be an opclause */
!                 Var           *other;

!                 other = (flag & SEL_RIGHT) ? get_rightop((Expr *) clause) :
!                     get_leftop((Expr *) clause);
!                 if (IsA(other, Const) || IsA(other, Param))
                  {
!                     Oid        opno = ((Oper *) ((Expr *) clause)->oper)->opno;
!                     RegProcedure oprrest = get_oprrest(opno);
!
!                     if (!oprrest)
!                         s2 = (Selectivity) 0.5;
!                     else
!                         s2 = restriction_selectivity(oprrest, opno,
!                                                      getrelid(relidx,
!                                                               root->rtable),
!                                                      attno,
!                                                      constval, flag);
!
!                     /*
!                      * If we reach here, we have computed the same result that
!                      * clause_selectivity would, so we can just use s2 if it's
!                      * the wrong oprrest.  But if it's the right oprrest, add
!                      * the clause to rqlist for later processing.
!                      */
!                     switch (oprrest)
!                     {
!                         case F_SCALARLTSEL:
!                             addRangeClause(&rqlist, clause, flag, true, s2);
!                             break;
!                         case F_SCALARGTSEL:
!                             addRangeClause(&rqlist, clause, flag, false, s2);
!                             break;
!                         default:
!                             /* Just merge the selectivity in generically */
!                             s1 = s1 * s2;
!                             break;
!                     }
!                     continue;    /* drop to loop bottom */
                  }
              }
          }
          /* Not the right form, so treat it generically. */