RE: const cast ? - Mailing list pgsql-sql

<p><font size="2">The problem is that there is no way of determining whether or not the sort order after the function
hasbeen executed will be the same as the sort order on the raw data.  For example, the sort order of n (-10..10) is
verydifferent to the sort order of abs(n).  So if I had an index on n, I could not use it for searching for
abs(n).</font><p><fontsize="2">So, if you always require the same function, you create a function index:</font><p><font
size="2">CREATEINDEX xxx ON t (date_part('year', i::date));</font><p><font size="2">Just remember, if you change
function,it will switch back to sequential scan, until you create a function index for the new function that you need
touse.</font><p><font size="2">Cheers...</font><br /><p><font size="2">MikeA</font><br /><br /><p><font
size="2">-----OriginalMessage-----</font><br /><font size="2">From: doj@wwws2.redaex.de [<a
href="mailto:doj@wwws2.redaex.de">mailto:doj@wwws2.redaex.de</a>]</font><br/><font size="2">Sent: 02 February 2001
13:22</font><br/><font size="2">To: PostgreSQL-SQL</font><br /><font size="2">Subject: [SQL] const cast ?</font><br
/><p><fontsize="2">Hello Postgres Users and Developers,</font><p><font size="2">I have the following
scenario:</font><br/><font size="2">create table t (i int);</font><br /><font size="2">create index ti on
t(i);</font><p><fontsize="2">Now this table is filled with some values and the table is vacuum analyzed.</font><p><font
size="2">NowI would like to run queries on this table which should use the index </font><br /><font size="2">whenever
possible,so they execute fast.</font><p><font size="2">If I try a simple query like: "select * from t where i=4" the
indexis used.</font><br /><font size="2">A query like: "select * from t where i=abs(4)" is using the index
too.</font><br/><font size="2">But if I use more complex functions like the following:</font><br /><font
size="2">"select* from t where i=date_part('year', '2001-01-01'::date)"</font><br /><font size="2">a sequential scan on
thetable is performed.</font><p><font size="2">Now I conclude that the planner/optimizer does not recognize that
the</font><br/><font size="2">date_part() function returns the same value upon each execution.</font><p><font
size="2">WhatI would like to know: Could we use some const-cast, so the optimzer gets</font><br /><font size="2">a hint
inoptimizing the query ?</font><br /><font size="2">I think of something like:</font><br /><font size="2">"select *
fromt where i=date_part('year', '2001-01-01'::date)::const"</font><p><font size="2">Would this be hard to implement, or
arethere any theoretical issues which</font><br /><font size="2">permit this. My thoughts are, that if the user
declaressomething as const,</font><br /><font size="2">although it might not always be const, the database should not
worryabout</font><br /><font size="2">the complete truth and just assume the statement as const.</font><p><font
size="2">OrIs this feature available already, and I have just missed the correct </font><br /><font
size="2">keyword?</font><p><fontsize="2">-- </font><br /><font size="2">--</font><br /><font size="2">--->
doj@redaex.de</font><code><fontsize="3"><br /><br />
**********************************************************************<br/> This email and any files transmitted with
itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed.
Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This
footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer
viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br
/></font></code>