From: | Alfred Perlstein <bright(at)wintelcom(dot)net> |
---|---|
To: | Joel Burton <jburton(at)scw(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: abstract: fix poor constant folding in 7.0.x, fixed in 7.1? |
Date: | 2000-12-08 00:20:09 |
Message-ID: | 20001207162009.Y16205@fw.wintelcom.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
* Joel Burton <jburton(at)scw(dot)org> [001207 15:52] wrote:
> > We had problem with a query taking way too long, basically
> > we had this:
> >
> > select
> > date_part('hour',t_date) as hour,
> > transval as val
> > from st
> > where
> > id = 500
> > AND hit_date >= '2000-12-07 14:27:24-08'::timestamp - '24
> > hours'::timespan AND hit_date <= '2000-12-07 14:27:24-08'::timestamp
> > ;
> >
> > turning it into:
> >
> > select
> > date_part('hour',t_date) as hour,
> > transval as val
> > from st
> > where
> > id = 500
> > AND hit_date >= '2000-12-07 14:27:24-08'::timestamp
> > AND hit_date <= '2000-12-07 14:27:24-08'::timestamp
> > ;
>
> Perhaps I'm being daft, but why should hit_date be both >= and <=
> the exact same time and date? (or did you mean to subtract 24
> hours from your example and forgot?)
Yes, typo.
> > (doing the -24 hours seperately)
> >
> > The values of cost went from:
> > (cost=0.00..127.24 rows=11 width=12)
> > to:
> > (cost=0.00..4.94 rows=1 width=12)
> >
> > By simply assigning each sql "function" a taint value for constness
> > one could easily reduce:
> > '2000-12-07 14:27:24-08'::timestamp - '24 hours'::timespan
> > to:
> > '2000-12-07 14:27:24-08'::timestamp
>
> You mean '2000-12-06', don't you?
Yes, typo. :)
>
> > Each function should have a marker that explains whether when given a
> > const input if the output might vary, that way subexpressions can be
> > collapsed until an input becomes non-const.
>
> There is "with (iscachable)".
>
> Does
>
> CREATE FUNCTION YESTERDAY(timestamp) RETURNS timestamp AS
> 'SELECT $1-''24 hours''::interval' WITH (iscachable)
>
> work faster?
It could be, but it could be done in the sql compiler/planner
explicitly to save me from myself, no?
--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-12-08 00:24:55 | Re: abstract: fix poor constant folding in 7.0.x, fixed in 7.1? |
Previous Message | Hiroshi Inoue | 2000-12-08 00:06:25 | Re: How to reset WAL enveironment |