Rostislav Opocensky <[EMAIL PROTECTED]> 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. */
