pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction-based index not used in a simple query
4+ messages / 2 participants
[nested] [flat]
* Function-based index not used in a simple query
@ 2000-05-30 18:31 Rostislav Opocensky <orbis@pictus.org>
0 siblings, 1 reply; 4+ messages in thread
From: Rostislav Opocensky @ 2000-05-30 18:31 UTC (permalink / raw)
To: pgsql-sql; +Cc: Pavel Noga <pnoga@unreal.cz>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function-based index not used in a simple query
@ 2000-05-30 21:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rostislav Opocensky <orbis@pictus.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2000-05-30 21:36 UTC (permalink / raw)
To: Rostislav Opocensky <orbis@pictus.org>; +Cc: pgsql-sql@postgresql.org, Pavel Noga <pnoga@unreal.cz>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function-based index not used in a simple query
@ 2000-05-31 10:33 Rostislav Opocensky <orbis@pictus.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Rostislav Opocensky @ 2000-05-31 10:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@postgresql.org, Pavel Noga <pnoga@unreal.cz>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function-based index not used in a simple query
@ 2000-05-31 15:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rostislav Opocensky <orbis@pictus.org>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2000-05-31 15:53 UTC (permalink / raw)
To: Rostislav Opocensky <orbis@pictus.org>; +Cc: pgsql-sql@postgresql.org, Pavel Noga <pnoga@unreal.cz>
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
inline patch 1: src/backend/optimizer/path/clausesel.c (+43/-34 lines) download inline-1.patch
*** src/backend/optimizer/path/clausesel.c.orig Tue May 30 00:26:44 2000
--- src/backend/optimizer/path/clausesel.c Wed May 31 11:38:53 2000
***************
*** 120,129 ****
Selectivity s2;
/*
! * See if it looks like a restriction clause with a constant. (If
! * it's not a constant we can't really trust the selectivity!) NB:
! * for consistency of results, this fragment of code had better
! * match what clause_selectivity() would do.
*/
if (varRelid != 0 || NumRelids(clause) == 1)
{
--- 120,131 ----
Selectivity s2;
/*
! * See if it looks like a restriction clause with a Const or Param
! * on one side. (Anything more complicated than that might not
! * behave in the simple way we are expecting.)
! *
! * NB: for consistency of results, this fragment of code had better
! * match what clause_selectivity() would do in the cases it handles.
*/
if (varRelid != 0 || NumRelids(clause) == 1)
{
***************
*** 134,174 ****
get_relattval(clause, varRelid,
&relidx, &attno, &constval, &flag);
! if (relidx != 0 && (flag & SEL_CONSTANT))
{
/* if get_relattval succeeded, it must be an opclause */
! Oid opno = ((Oper *) ((Expr *) clause)->oper)->opno;
! RegProcedure oprrest = get_oprrest(opno);
! if (!oprrest)
! s2 = (Selectivity) 0.5;
! else
! s2 = restriction_selectivity(oprrest, opno,
! getrelid(relidx,
! root->rtable),
! attno,
! constval, flag);
!
! /*
! * If we reach here, we have computed the same result that
! * clause_selectivity would, so we can just use s2 if it's
! * the wrong oprrest. But if it's the right oprrest, add
! * the clause to rqlist for later processing.
! */
! switch (oprrest)
{
! case F_SCALARLTSEL:
! addRangeClause(&rqlist, clause, flag, true, s2);
! break;
! case F_SCALARGTSEL:
! addRangeClause(&rqlist, clause, flag, false, s2);
! break;
! default:
! /* Just merge the selectivity in generically */
! s1 = s1 * s2;
! break;
}
- continue; /* drop to loop bottom */
}
}
/* Not the right form, so treat it generically. */
--- 136,183 ----
get_relattval(clause, varRelid,
&relidx, &attno, &constval, &flag);
! if (relidx != 0)
{
/* if get_relattval succeeded, it must be an opclause */
! Var *other;
! other = (flag & SEL_RIGHT) ? get_rightop((Expr *) clause) :
! get_leftop((Expr *) clause);
! if (IsA(other, Const) || IsA(other, Param))
{
! Oid opno = ((Oper *) ((Expr *) clause)->oper)->opno;
! RegProcedure oprrest = get_oprrest(opno);
!
! if (!oprrest)
! s2 = (Selectivity) 0.5;
! else
! s2 = restriction_selectivity(oprrest, opno,
! getrelid(relidx,
! root->rtable),
! attno,
! constval, flag);
!
! /*
! * If we reach here, we have computed the same result that
! * clause_selectivity would, so we can just use s2 if it's
! * the wrong oprrest. But if it's the right oprrest, add
! * the clause to rqlist for later processing.
! */
! switch (oprrest)
! {
! case F_SCALARLTSEL:
! addRangeClause(&rqlist, clause, flag, true, s2);
! break;
! case F_SCALARGTSEL:
! addRangeClause(&rqlist, clause, flag, false, s2);
! break;
! default:
! /* Just merge the selectivity in generically */
! s1 = s1 * s2;
! break;
! }
! continue; /* drop to loop bottom */
}
}
}
/* Not the right form, so treat it generically. */
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-05-31 15:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-30 18:31 Function-based index not used in a simple query Rostislav Opocensky <orbis@pictus.org>
2000-05-30 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-31 10:33 ` Rostislav Opocensky <orbis@pictus.org>
2000-05-31 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox