Horton

Thread

  1. Function-based index not used in a simple query

    Rostislav Opocensky <orbis@pictus.org> — 2000-05-30T18:31:39Z

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

  2. Re: Function-based index not used in a simple query

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-30T21:36:36Z

    Rostislav Opocensky <orbis@pictus.org> writes:
> 	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)

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
that trunc_to_day('28.5.2000') is a constant, because you haven't told
it that it can pre-evaluate that function call --- and for all it knows,
the function might generate different values when evaluated at different
rows of the table, rendering an indexscan incorrect.  (Consider a
function like random() ...)  If you mark trunc_to_day() as a "cachable"
function then the system will know it's safe to collapse the righthand
side to a constant:

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

This is a little bit of a cheat for this function, because what
isCachable actually implies is that the function's value depends
on the presented arguments *and nothing else*.  I believe date_trunc
depends on the current TZ setting, so it's not really cachable.
You could possibly get away with it for your application, but you
might run into problems like this:

1. You write trunc_to_day('28.5.2000') in a table's column
   default expression, stored rule, or some such.  The system
   decides it can fold that expression down to a simple constant,
   giving you (say) 2000-05-20 00:00-04 --- ie, midnight in
   whatever timezone you are using.  That value gets stored in
   the column or rule definition.

2. You then access the database while running with a different
   TZ setting.  You wonder why you don't get the answers you expected.

Come to think of it, your index on trunc_to_day() is fundamentally
risky because different backends running with different TZ settings
would generate different index entries for the "same" data entry.
For example, if you make an entry dated '28.5.2000' while running in
one timezone, and then try to query with "= trunc_to_day('28.5.2000')"
while running in another timezone, you won't match that entry if the
index is used, because the timestamp generated on-the-fly from
trunc_to_day('28.5.2000') won't equal the one stored in the index.

(Hmm ... I wonder if that suggests that we shouldn't allow indexes
to be built using functions that are not isCachable?)

You could avoid some of these risks by having trunc_to_day return
a 'date' rather than a 'timestamp', thereby removing at least some
of the timezone dependency from the index definition.

			regards, tom lane

  3. Re: Function-based index not used in a simple query

    Rostislav Opocensky <orbis@pictus.org> — 2000-05-31T10:33:05Z

    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
> that trunc_to_day('28.5.2000') is a constant, because you haven't told
> it that it can pre-evaluate that function call --- and for all it knows,
[...]

Dear Tom,

thank you for the precise explanation for my problem!

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?

Best regards
		Orbis

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

  4. Re: Function-based index not used in a simple query

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-31T15:53:25Z

    Rostislav Opocensky <orbis@pictus.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

  5. short query becomes long

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-01T18:24:39Z

    hi,
  we have a weird situation here.  we have a table of approx. 10k rows
representing accumulated activity by specific customers.  as information 
is gathered those customers rows are updated.  the number of rows does not
increase unless we get a new customer so that is not a factor.  the table 
is defined as follows:

      Table "account_summary_02"
  Attribute  |    Type     | Modifier
-------------+-------------+----------
 bill_br_id  | bigint      | not null
 cust_id     | varchar(15) | not null
 btn_id      | varchar(15) | not null
 ln_id       | varchar(15) | not null
 ct_key      | float8      | not null
 as_quantity | float8      | not null
 as_charges  | float8      | not null
 as_count    | float8      | not null
Index: account_summary_02_unq_idx

the index is on the first 5 columns.  here's the situation.  after about
50,000
updates, which fly right along, the process begins to really bog down.  we
perform 
a vacuum analzye and it speeds right up again.  my question is, is there a way
to perform these updates, potentially 500k to 1 million in a day, without
having 
to vacuum so frequently?  maybe some setting or parameter to be changed?
the update
query is doing an index scan.  

mikeo

  6. Re: short query becomes long

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-02T02:31:59Z

    mikeo <mikeo@spectrumtelecorp.com> writes:
> after about 50,000 updates, which fly right along, the process begins
> to really bog down.  we perform a vacuum analzye and it speeds right
> up again.

A plain "vacuum" should do the job in a bit less time.  The problem
is you need to reclaim the space occupied by deleted versions of rows.
No way around that with the current storage manager: vacuum is the
only way to get rid of the wasted space.

			regards, tom lane