Thread: 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).