[SQL] Function-based index not used in a simple query

[Rostislav Opocensky]

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 <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111