Function-based index not used in a simple query - Mailing list pgsql-sql

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
ALIASFOR $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_dayAND            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@pictus.org> <orbis@unreal.cz> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111