Обсуждение: Performance of a query

Greetings all!

So I have had a look at the documentation to see what options I have in
separating the "select now() - '2 hours'::interval" from the rest of my
query. As far as I can tell, I have the following options:

(1) Do it in my calling code (Perl). Create two statements, execute the
first to get the time interval, then the second to get the results.

(2) Create a custom function that will enable me to have a variable. This
is now feasible with SETOF. Any issues with size of result set that would
be any different than if I do it directly?

(3) Use a temporary table. Ugly if the statement appears more than once in
the complex expression. Not sure if the constant folding would work
without some experimentation. Performance hits with creating temporary
tables?

(4) Use code to generate the time in my calling code, and then execute the
one statement. Would probably work, but I would need to see if all my
cases fall into this strategy.

Overall, none of them are appealing. Doesn't look like I have the option
of using PL/pgSQL without going through the whole process of creating a
function (which is possible in Oracle, as far as I can recall).

Any other appraches or recommended coding methods?

Regards,
Ed

On Wed, Jun 11, 2003 at 20:11:02 -0400,
  Edmund Dengler <edmundd@eSentire.com> wrote:
> Greetings all!
>
> So I have had a look at the documentation to see what options I have in
> separating the "select now() - '2 hours'::interval" from the rest of my
> query. As far as I can tell, I have the following options:

How about using local_timestamp instead of now? It returns a timestamp
instead of a timestamptz and I suspect will do what you want.
Whatever you are comparing it to will also need to be of type
timestamp. I suspect that you should be careful when setting timezone in your
transaction when doing things this way.

Doesn't seem to exist.

select current_user; ==> works.

select local_timestamp; ==> "ERROR: Attribute "local_timestamp" not
found".

Is this perhaps a deprecated constant? (Currently running 7.3.3)

Regards,
Ed

On Wed, 11 Jun 2003, Bruno Wolff III wrote:

> On Wed, Jun 11, 2003 at 20:11:02 -0400,
>   Edmund Dengler <edmundd@eSentire.com> wrote:
> > Greetings all!
> >
> > So I have had a look at the documentation to see what options I have in
> > separating the "select now() - '2 hours'::interval" from the rest of my
> > query. As far as I can tell, I have the following options:
>
> How about using local_timestamp instead of now? It returns a timestamp
> instead of a timestamptz and I suspect will do what you want.
> Whatever you are comparing it to will also need to be of type
> timestamp. I suspect that you should be careful when setting timezone in your
> transaction when doing things this way.
>

On Wed, Jun 11, 2003 at 20:51:39 -0400,
  Edmund Dengler <edmundd@eSentire.com> wrote:
> Doesn't seem to exist.
>
> select current_user; ==> works.
>
> select local_timestamp; ==> "ERROR: Attribute "local_timestamp" not
> found".
>
> Is this perhaps a deprecated constant? (Currently running 7.3.3)

There isn't an underline. Sorry about that. It is in both 7.3 and 7.4.
Its documented under date time functions under operators and functions.

Tried the query. Looks like this suffers the same fate as now(): the
planner does not deal with this as a pseudo constant, so therefore the
estimator over-estimates the number of possible rows, and thereby chooses
the wrong operator (a "merge join") to apply.

Regards,
Ed

On Wed, 11 Jun 2003, Bruno Wolff III wrote:

> On Wed, Jun 11, 2003 at 20:51:39 -0400,
>   Edmund Dengler <edmundd@eSentire.com> wrote:
> > Doesn't seem to exist.
> >
> > select current_user; ==> works.
> >
> > select local_timestamp; ==> "ERROR: Attribute "local_timestamp" not
> > found".
> >
> > Is this perhaps a deprecated constant? (Currently running 7.3.3)
>
> There isn't an underline. Sorry about that. It is in both 7.3 and 7.4.
> Its documented under date time functions under operators and functions.
>

On Wed, Jun 11, 2003 at 21:13:52 -0400,
  Edmund Dengler <edmundd@eSentire.com> wrote:
> Tried the query. Looks like this suffers the same fate as now(): the
> planner does not deal with this as a pseudo constant, so therefore the
> estimator over-estimates the number of possible rows, and thereby chooses
> the wrong operator (a "merge join") to apply.

I tried some more variations and couldn't get it to use an index.
It seems odd that a timestamp without time zone is getting treated
as not constant (presumably because the timezone could change).