Re: Function-based index not used in a simple query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rostislav Opocensky <orbis(at)pictus(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org, Pavel Noga <pnoga(at)unreal(dot)cz>
Subject: Re: Function-based index not used in a simple query
Date: 2000-05-31 15:53:25
Message-ID: 6979.959788405@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Rostislav Opocensky <orbis(at)pictus(dot)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

Attachment Content-Type Size
unknown_filename text/plain 3.7 KB

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rick Parker 2000-05-31 16:51:57 psql problem
Previous Message Wallingford, Ted 2000-05-31 12:45:55 create view security