You can create a function with the IsCacheable attribute...
CREATE FUNCTION my_date_part(date) RETURNS int4 AS 'SELECT date_part('year', $1);
' LANGUAGE 'sql' WITH iscachable();
(This can be found in the create function docs, or in Bruce's book -- both
available on the website)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Fri, 2 Feb 2001 doj@wwws2.redaex.de wrote:
> Hello Postgres Users and Developers,
>
> I have the following scenario:
> create table t (i int);
> create index ti on t(i);
>
> Now this table is filled with some values and the table is vacuum analyzed.
>
> Now I would like to run queries on this table which should use the index
> whenever possible, so they execute fast.
>
> If I try a simple query like: "select * from t where i=4" the index is used.
> A query like: "select * from t where i=abs(4)" is using the index too.
> But if I use more complex functions like the following:
> "select * from t where i=date_part('year', '2001-01-01'::date)"
> a sequential scan on the table is performed.
>
> Now I conclude that the planner/optimizer does not recognize that the
> date_part() function returns the same value upon each execution.
>
> What I would like to know: Could we use some const-cast, so the optimzer gets
> a hint in optimizing the query ?
> I think of something like:
> "select * from t where i=date_part('year', '2001-01-01'::date)::const"
>
> Would this be hard to implement, or are there any theoretical issues which
> permit this. My thoughts are, that if the user declares something as const,
> although it might not always be const, the database should not worry about
> the complete truth and just assume the statement as const.
>
> Or Is this feature available already, and I have just missed the correct
> keyword?
>
> --
> --
> ---> doj@redaex.de
>
