pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rostislav Opocensky <orbis@pictus.org>
Cc: pgsql-sql@postgresql.org, Pavel Noga <pnoga@unreal.cz>
Subject: Re: Function-based index not used in a simple query
Date: Wed, 31 May 2000 11:53:25 -0400
Message-ID: <6979.959788405@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.10.10005311006160.15209-100000@ns.rl.cesnet.cz>
References: <Pine.LNX.4.10.10005311006160.15209-100000@ns.rl.cesnet.cz>

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
inline patch 1: src/backend/optimizer/path/clausesel.c (+43/-34 lines) download inline-1.patch*** 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. */

view thread (4+ messages)

Message-ID: <6979.959788405@sss.pgh.pa.us>
Permalink:  ../6979.959788405@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/6979.959788405@sss.pgh.pa.us

 ·  · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, orbis@pictus.org, pnoga@unreal.cz
  Subject: Re: Function-based index not used in a simple query
  In-Reply-To: <6979.959788405@sss.pgh.pa.us>
  References: <Pine.LNX.4.10.10005311006160.15209-100000@ns.rl.cesnet.cz> <6979.959788405@sss.pgh.pa.us>

* 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