Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


In response to

Responses

pgsql-sql by date

Next:From: Rick ParkerDate: 2000-05-31 16:51:57
Subject: psql problem
Previous:From: Wallingford, TedDate: 2000-05-31 12:45:55
Subject: create view security

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group