Received: from wwws2.redaex.de (wwws2.redaex.de [195.126.101.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f12CUOq68599 for ; Fri, 2 Feb 2001 07:30:25 -0500 (EST) (envelope-from doj@wwws2.redaex.de) Received: (from doj@localhost) by wwws2.redaex.de (8.9.3/8.9.3) id OAA03574; Fri, 2 Feb 2001 14:22:13 +0100 Message-ID: <20010202142213.32170@wwws2.redaex.de> Date: Fri, 2 Feb 2001 14:22:13 +0100 From: To: PostgreSQL-SQL Subject: const cast ? References: <20010131223906.A1337@frithjof> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.81e In-Reply-To: <20010131223906.A1337@frithjof>; from Albert REINER on Wed, Jan 31, 2001 at 10:39:06PM +0100 X-Archive-Number: 200102/9 X-Sequence-Number: 1177 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