Re: [SQL] using calculated column in where-clause
> -Mensaje original- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > Enviado el: Miércoles, 18 de Junio de 2008 17:47 > Para: Fernando Hevia > > > > For complex calculations I have obtained better performance using > > nested queries. For example: > > > > select a, b, c select > > ( select a, b, a*b as c from ta) subquery1 where c = 2; > > > > This nesting is probably overhead in such a simple case as > this, but > > in more complex ones and specially with volatile functions it will > > provide an improvement. > > I was under the impresion from previous discussions that the > query planner flattened these out to be the same query. Do > you get different query plans when you re-arrange this way? > Take a look at this example (tried on 8.2.7 & 8.1.11): create or replace function test(p1 integer, p2 integer) returns integer[] as $BODY$ declare retval integer[]; begin raise info 'called test(%, %)', p1, p2; retval[0] = p1 + p2; retval[1] = p1 * p2; retval[2] = p1 - p2; return retval; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; -- In this case function test is called three times: pg=# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod, (test(1, 2))[2] as dif; INFO: called test(1, 2) INFO: called test(1, 2) INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) -- In this case function test is called only once: pg=# select res[0] as sum, res[1] as prod, res[2] as dif from pg-# (select (test(1, 2))::integer[] as res) t ; INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) I assume the second form will perform better since test is being called only once. I might be missing something in this assumption but at first glance it seems pretty straightforward. Regards, Fernando. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
> > -Mensaje original- > > De: Scott Marlowe [mailto:[EMAIL PROTECTED] Enviado el: > > Miércoles, 18 de Junio de 2008 17:47 > > Para: Fernando Hevia > > > > > > > For complex calculations I have obtained better performance using > > > nested queries. For example: > > > > > > select a, b, c select > > > ( select a, b, a*b as c from ta) subquery1 where c = 2; > > > > > > This nesting is probably overhead in such a simple case as > > this, but > > > in more complex ones and specially with volatile > functions it will > > > provide an improvement. > > > > I was under the impresion from previous discussions that the query > > planner flattened these out to be the same query. Do you get > > different query plans when you re-arrange this way? > > > > Take a look at this example (tried on 8.2.7 & 8.1.11): > > create or replace function test(p1 integer, p2 integer) > returns integer[] as $BODY$ declare >retval integer[]; > begin >raise info 'called test(%, %)', p1, p2; >retval[0] = p1 + p2; >retval[1] = p1 * p2; >retval[2] = p1 - p2; >return retval; > end; > $BODY$ > LANGUAGE 'plpgsql' IMMUTABLE; > > -- In this case function test is called three times: > pg=# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod, > (test(1, 2))[2] as dif; > INFO: called test(1, 2) > INFO: called test(1, 2) > INFO: called test(1, 2) > sum | prod | dif > -+--+- >3 |2 | -1 > (1 row) > > > -- In this case function test is called only once: > pg=# select res[0] as sum, res[1] as prod, res[2] as dif from > pg-# (select (test(1, 2))::integer[] as res) t ; > INFO: called test(1, 2) > sum | prod | dif > -+--+- >3 |2 | -1 > (1 row) > > I assume the second form will perform better since test is > being called only once. > I might be missing something in this assumption but at first > glance it seems pretty straightforward. > > Regards, > Fernando. > --Follow up-- When I use one of the inner-query columns as a condition for the outer-query the function is being called again: pg=# select res[0] as sum, res[1] as prod, res[2] as dif from pg-# (select (test(1, 2))::integer[] as res) t pg-# where res[0] = 3; INFO: called test(1, 2) INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) Seems this blows away my theory, at least part of it. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
"Fernando Hevia" <[EMAIL PROTECTED]> writes: > -- In this case function test is called only once: > pg=# select res[0] as sum, res[1] as prod, res[2] as dif from > pg-# (select (test(1, 2))::integer[] as res) t ; That's an implementation artifact, not a guaranteed behavior; if you change the example a bit you'll find multiple calls happening. (In recent releases you'd actually have a better chance of not having multiple calls if you'd declared it volatile instead of immutable.) regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] "TZ"/"tz" not supported
Hi,
Execution of the following statement aborts with the error message in
the Subject:
select to_timestamp('2008-06-20 02:30:00 GMT', '-MM-DD HH24:MI:SS TZ');
Does this message mean that this particular PostgreSQL installation
doesn't support timezones?
--
select * from pg_catalog.pg_timezone_abbrevs where abbrev = 'GMT';
abbrev | utc_offset | is_dst
++
GMT| 00:00:00 | f
(1 row)
--
The database version is 8.3.0.
Any help appreciated,
Peter
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "TZ"/"tz" not supported
am Fri, dem 20.06.2008, um 7:51:50 +0200 mailte Peter Kovacs folgendes:
> Hi,
>
> Execution of the following statement aborts with the error message in
> the Subject:
>
> select to_timestamp('2008-06-20 02:30:00 GMT', '-MM-DD HH24:MI:SS TZ');
You can use:
test=*# select '2008-06-20 02:30:00 GMT'::timestamptz;
timestamptz
2008-06-20 04:30:00+02
(1 row)
helps that?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "TZ"/"tz" not supported
Thank you, Andreas! Your advice is very useful to me.
I would still be interested why "TZ" is not accepted in the format string.
Thanks
Peter
On Fri, Jun 20, 2008 at 8:15 AM, A. Kretschmer
<[EMAIL PROTECTED]> wrote:
> am Fri, dem 20.06.2008, um 7:51:50 +0200 mailte Peter Kovacs folgendes:
>> Hi,
>>
>> Execution of the following statement aborts with the error message in
>> the Subject:
>>
>> select to_timestamp('2008-06-20 02:30:00 GMT', '-MM-DD HH24:MI:SS TZ');
>
> You can use:
>
> test=*# select '2008-06-20 02:30:00 GMT'::timestamptz;
> timestamptz
>
> 2008-06-20 04:30:00+02
> (1 row)
>
>
>
> helps that?
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "TZ"/"tz" not supported
am Fri, dem 20.06.2008, um 8:35:10 +0200 mailte Peter Kovacs folgendes: > Thank you, Andreas! Your advice is very useful to me. > > I would still be interested why "TZ" is not accepted in the format string. I think because TZ is only useful for displaying a timestamptz and not for internal representation and/or calculation. For displaying you can use to_char(timestamptz, format-string). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
