Received: from fileserver002.intecsystems.co.uk ([194.200.14.125]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f12Cwpq70220 for ; Fri, 2 Feb 2001 07:58:51 -0500 (EST) (envelope-from Michael.Ansley@intec-telecom-systems.com) Received: from fileserver002.intecsystems.co.uk (unverified) by fileserver002.intecsystems.co.uk (Content Technologies SMTPRS 4.1.5) with ESMTP id ; Fri, 2 Feb 2001 13:02:05 +0000 Received: by FILESERVER002 with Internet Mail Service (5.5.2650.21) id <1BFBGC1R>; Fri, 2 Feb 2001 13:02:05 -0000 Message-ID: <7F124BC48D56D411812500D0B747251480F3BF@FILESERVER002> From: Michael Ansley To: "'doj@wwws2.redaex.de'" , PostgreSQL-SQL Subject: RE: const cast ? Date: Fri, 2 Feb 2001 13:02:05 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C08D18.57B96BF8" X-Archive-Number: 200102/10 X-Sequence-Number: 1178 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C08D18.57B96BF8 Content-Type: text/plain; charset="iso-8859-1" The problem is that there is no way of determining whether or not the sort order after the function has been executed will be the same as the sort order on the raw data. For example, the sort order of n (-10..10) is very different 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). So, if you always require the same function, you create a function index: CREATE INDEX xxx ON t (date_part('year', i::date)); 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 to use. Cheers... MikeA -----Original Message----- From: doj@wwws2.redaex.de [mailto:doj@wwws2.redaex.de] Sent: 02 February 2001 13:22 To: PostgreSQL-SQL Subject: [SQL] const cast ? 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 ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ********************************************************************** ------_=_NextPart_001_01C08D18.57B96BF8 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] const cast ?

The problem is that there is no way of determining whethe= r or not the sort order after the function has been executed will be the sa= me as the sort order on the raw data.  For example, the sort order of = n (-10..10) is very different to the sort order of abs(n).  So if I ha= d an index on n, I could not use it for searching for abs(n).

So, if you always require the same function, you create a= function index:

CREATE INDEX xxx ON t (date_part('year', i::date));

Just remember, if you change function, it will switch bac= k to sequential scan, until you create a function index for the new functio= n that you need to use.

Cheers...


MikeA



-----Original Message-----
From: doj@wwws2.redaex.de [mailto:doj@wwws2.redaex.de]
Sent: 02 February 2001 13:22
To: PostgreSQL-SQL
Subject: [SQL] const cast ?


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 i= s vacuum analyzed.

Now I would like to run queries on this table which shoul= d use the index
whenever possible, so they execute fast.

If I try a simple query like: "select * from t where= i=3D4" the index is used.
A query like: "select * from t where i=3Dabs(4)&quo= t; is using the index too.
But if I use more complex functions like the following:<= /FONT>
"select * from t where i=3Ddate_part('year', '2001-= 01-01'::date)"
a sequential scan on the table is performed.

Now I conclude that the planner/optimizer does not recogn= ize that the
date_part() function returns the same value upon each ex= ecution.

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=3Ddate_part('year', '2001-= 01-01'::date)::const"

Would this be hard to implement, or are there any theoret= ical issues which
permit this. My thoughts are, that if the user declares = something as const,
although it might not always be const, the database shou= ld not worry about
the complete truth and just assume the statement as cons= t.

Or Is this feature available already, and I have just mis= sed the correct
keyword?

--
--
---> doj@redaex.de



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************
------_=_NextPart_001_01C08D18.57B96BF8--