pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE: const cast ?
4+ messages / 4 participants
[nested] [flat]

* RE: const cast ?
@ 2001-02-02 13:02 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Ansley @ 2001-02-02 13:02 UTC (permalink / raw)
  To: 'doj@wwws2.redaex.de' <doj@wwws2.redaex.de>; pgsql-sql

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
**********************************************************************

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* const cast ?
@ 2001-02-02 13:22 doj@wwws2.redaex.de
  2001-02-02 20:48 ` Re: const cast ? Michael Fork <mfork@toledolink.com>
  0 siblings, 1 reply; 4+ messages in thread

From: doj@wwws2.redaex.de @ 2001-02-02 13:22 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: const cast ?
  2001-02-02 13:22 const cast ? doj@wwws2.redaex.de
@ 2001-02-02 20:48 ` Michael Fork <mfork@toledolink.com>
  2001-02-03 07:38   ` Re: const cast ? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Michael Fork @ 2001-02-02 20:48 UTC (permalink / raw)
  To: doj@wwws2.redaex.de; +Cc: pgsql-sql

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
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: const cast ?
  2001-02-02 13:22 const cast ? doj@wwws2.redaex.de
  2001-02-02 20:48 ` Re: const cast ? Michael Fork <mfork@toledolink.com>
@ 2001-02-03 07:38   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-02-03 07:38 UTC (permalink / raw)
  To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql

Michael Fork <mfork@toledolink.com> writes:
> 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();

The reason date_part --- and most other datetime-related functions ---
is not marked iscachable already is the existence of the special value
CURRENT in some of the datetime datatypes, which makes the result of
any datetime function potentially dependent on when you execute it.
I believe we have agreed that CURRENT is evil and should be eliminated,
but it hasn't gotten done yet.  See past discussions in the pghackers
archives.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-02-03 07:38 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-02 13:02 RE: const cast ? Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-02 13:22 const cast ? doj@wwws2.redaex.de
2001-02-02 20:48 ` Michael Fork <mfork@toledolink.com>
2001-02-03 07:38   ` 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