Function-based index not used in a simple query

Hi all,

I have run into a problem trying to optimize a select from a single table.
Due to the nature of the queries our frontends issue most of the time, we
have created an index that reduces the processing time a lot. The problem
(index not being used) arose when I tried to do a select with non-constant
parameters to the WHERE condition.

The table definition looks like this:

CREATE TABLE vals (
timestamp timestamp NOT NULL,
agent varchar(15) NOT NULL,
var varchar(64) NOT NULL,
val text NOT NULL
);

The index definition is based on a user-defined function:

CREATE FUNCTION trunc_to_day(timestamp) RETURNS timestamp AS '
DECLARE tstamp ALIAS FOR $1;
BEGIN
RETURN date_trunc(''day'', tstamp);
END;
' LANGUAGE 'plpgsql';

CREATE INDEX vals_days
ON vals (trunc_to_day(timestamp) timestamp_ops);

A typical query looks like this (additional conditions removed from the
WHERE condition as well as additional GROUP BY and ORDER BY clauses):

SELECT *
FROM vals
WHERE trunc_to_day(timestamp) = '28.5.2000';

Explain on this query produces:

Index Scan using vals_days on vals (cost=0.00..8.16 rows=10 width=44)

Now, when I try to do the same with a slightly changed comparison expression
in the WHERE clause, an optimizer decides to sequentially scan the table:

SELECT *
FROM vals
WHERE trunc_to_day(timestamp) = trunc_to_day('28.5.2000');

Seq Scan on vals (cost=0.00..27.50 rows=10 width=44)

Actually, the problem first appeared in a stored procedure:

CREATE FUNCTION detector(timestamp, varchar) RETURNS float AS '
DECLARE
check_time ALIAS FOR $1;
check_agent ALIAS FOR $2;

from_time timestamp;
to_time timestamp;
from_day timestamp;
to_day timestamp;
rssi_var vars.var%TYPE;
avg_rssi float;
BEGIN
from_time = check_time;
from_day = trunc_to_day(from_time);

to_time = check_time + ''1 day''::interval;
to_day = trunc_to_day(to_time);

SELECT INTO rssi_var var || ''%''
FROM vars
WHERE name = ''brzAvrgRssi'';

SELECT INTO avg_rssi AVG(val::float)
FROM vals
WHERE trunc_to_day(timestamp) BETWEEN from_day AND to_day AND
timestamp BETWEEN from_time AND to_time AND
agent = check_agent AND
var LIKE rssi_var;

IF avg_rssi IS NULL THEN
RAISE EXCEPTION ''There are no values for % and %.'',
check_time, check_agent;
END IF;

RETURN avg_rssi;
END;
' LANGUAGE 'plpgsql';

Sorry for a lengthy listing, but I didn't want to omit something important
accidentally. It seems the optimizer chooses to seq-scan whenever there is
anything else than a simple string constant in the WHERE condition.

After reading the thread ``Index not used in functions in 7.0'' two weeks ago
in this list, I have experimented with typecasting extensively, trying to add
``::timestamp'' wherever possible to the query, but with no success.

The PostgreSQL version is 7.0.0 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66.

Thanks in advance for any advices!

Orbis

--
Rostislav Opocensky <orbis(at)pictus(dot)org> <orbis(at)unreal(dot)cz> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111