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

Function-based index not used in a simple query

From: Rostislav Opocensky <orbis(at)pictus(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Cc: Pavel Noga <pnoga(at)unreal(dot)cz>
Subject: Function-based index not used in a simple query
Date: 2000-05-30 18:31:39
Message-ID: Pine.LNX.4.10.10005301856210.12648-100000@ns.rl.cesnet.cz (view raw or flat)
Thread:
Lists: 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 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


Responses

pgsql-sql by date

Next:From: mikeoDate: 2000-05-30 19:07:59
Subject: Re: Re: [SQL] remove line type?
Previous:From: Tom LaneDate: 2000-05-30 17:41:55
Subject: Re: Re: [SQL] remove line type?

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