[HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
Hello I testing a simple use case and I was surprised with very slow execution of SQL functions create or replace function empty_string1(text) returns bool as $$ select $1 is NULL or $1 = '' $$ language sql; postgres=# select count(empty_string1(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,10); count 10 (1 row) Time: 448.616 ms little bit updated function is much faster create or replace function empty_string2(text) returns bool as $$ select coalesce($1,'') = '' $$ language sql; postgres=# select count(empty_string2(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,10); count 10 (1 row) Time: 64.437 ms just null test function is fast too (or just empty str function) postgres=# create or replace function empty_string1(text) returns bool as $$select $1 is NULL $$ language sql; CREATE FUNCTION Time: 21.929 ms postgres=# select count(empty_string1(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,10); count 10 (1 row) Time: 48.554 ms Is strange - so slow function can be replaced by plpgsql function and it's faster postgres=# create or replace function empty_string1(text) returns bool as $$begin return $1 is null or $1 = ''; end$$ language plpgsql immutable; CREATE FUNCTION Time: 70.359 ms postgres=# select count(empty_string1(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,10); count 10 (1 row) Time: 220.131 ms Tested on 9.1 without assertions Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
On 29 Aug 2010, at 13:20, Pavel Stehule pavel.steh...@gmail.com wrote: Is strange - so slow function can be replaced by plpgsql function and it's faster All your SQL language functions were VOLATILE. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
Hello 2010/8/29 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: On 29 Aug 2010, at 13:20, Pavel Stehule pavel.steh...@gmail.com wrote: Is strange - so slow function can be replaced by plpgsql function and it's faster All your SQL language functions were VOLATILE. It's not a problem - planner see inside SQL function - so you don't need set a flags. Regards Pavel Stehule Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
On Thu, Aug 26, 2010 at 22:59, Cristian Bittel cbit...@gmail.com wrote: I still believe this exit code 128 is related to pgAdmin opened during the clossing session on Remote Desktop. I have a Windows user login wich is not administrator just no privileged user, it cannot start/stop services, just monitoring. With pgAdmin window opened inside my disconected session, as Administrator if I close the another disconnected session, Postgres exit with 128 code. If the closing of a session on the remote desktop can affect a *service* then frankly that sounds like a serious isolation bug in Windows itself. The postmaster grabs the handle of the process when it's started and waits on that - that should never be affected by something in a different session. I think it's more likely that Windows just looses track when you terminate a lot of processes at once, and randomly kills off something - or at least *indicates* that something has been killed off. Did you reproduce this behavior? No, AFAIK nobody has managed to reproduce this behavior in any kind of consistent way. It's certainly been seen more than once in many places, but not consistently reproducible. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
Pavel Stehule pavel.steh...@gmail.com writes: I testing a simple use case and I was surprised with very slow execution of SQL functions The one case is inline-able and the other not (because it would result in double evaluation of the volatile function random()). See EXPLAIN VERBOSE. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
2010/8/29 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I testing a simple use case and I was surprised with very slow execution of SQL functions The one case is inline-able and the other not (because it would result in double evaluation of the volatile function random()). See EXPLAIN VERBOSE. I understand now. So it means general advice - don't use a boolean operators in SQL function? This issue should be documented somewhere? Regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
Pavel Stehule pavel.steh...@gmail.com writes: 2010/8/29 Tom Lane t...@sss.pgh.pa.us: The one case is inline-able and the other not (because it would result in double evaluation of the volatile function random()). See EXPLAIN VERBOSE. I understand now. So it means general advice - don't use a boolean operators in SQL function? This issue should be documented somewhere? It has nothing to do with boolean operators, just double evaluation. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
2010/8/29 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/8/29 Tom Lane t...@sss.pgh.pa.us: The one case is inline-able and the other not (because it would result in double evaluation of the volatile function random()). See EXPLAIN VERBOSE. I understand now. So it means general advice - don't use a boolean operators in SQL function? This issue should be documented somewhere? It has nothing to do with boolean operators, just double evaluation. sure. I was blind. I have a question. It is possible do following optimalisation? I can write a function CREATE OR REPLACE FUNCTION estring(text) RETURNS bool AS $$ SELECT x IS NULL || x = '' FROM (VALUES($1)) g(x) $$ LANGUAGE sql; Now this function isn't inlined, because optimaliser doesn't know a VALUES clause. But with this knowleade, this can be a protection before double evaluation. Or different way - generate_subplan with parameters - it is still faster, than plpgsql or not inlined sql. p.s. this query is badly planed postgres=# select sum((select x is null or x = '' from (values(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) g(x) )::int) FROM generate_series(1,10); sum 10 (1 row) for corect behave a had to append a second variable postgres=# select sum((select x is null or x = '' and i = i from (values(CASE WHEN random() 0.5 THEN NULL ELSE 'x' END)) g(x) )::int) FROM generate_series(1,10) x(i); sum --- 50036 (1 row) Regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
On Sun, Aug 29, 2010 at 11:23:29AM -0400, Tom Lane wrote: Pavel Stehule pavel.steh...@gmail.com writes: I understand now. So it means general advice - don't use a boolean operators in SQL function? This issue should be documented somewhere? It has nothing to do with boolean operators, just double evaluation. I was wondering, wouldn't it be possible to avoid the double evaluation by simply creating an extra slot for the intermediate value. So you get: $1 = CASE WHEN random() 0.5 THEN NULL ELSE 'x' END $2 = $1 IS NULL or $1 = '' Sort of the way WITH works, but then for parts of expressions. I don't believe currently expressions can refer to Vars at the same level (it would make projections somewhat messy) but if you could fix that you could avoid the double evaluation and still have decent performance, right? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
Martijn van Oosterhout klep...@svana.org writes: On Sun, Aug 29, 2010 at 11:23:29AM -0400, Tom Lane wrote: It has nothing to do with boolean operators, just double evaluation. I was wondering, wouldn't it be possible to avoid the double evaluation by simply creating an extra slot for the intermediate value. Possibly, but the trick would be to figure out when to evaluate the values so that it would still behave the same as without inlining. I don't think the existing Param mechanism could do this without some additional help. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance issue: logical operators are slow inside SQL function: missing optimalization?
2010/8/29 Tom Lane t...@sss.pgh.pa.us: Martijn van Oosterhout klep...@svana.org writes: On Sun, Aug 29, 2010 at 11:23:29AM -0400, Tom Lane wrote: It has nothing to do with boolean operators, just double evaluation. I was wondering, wouldn't it be possible to avoid the double evaluation by simply creating an extra slot for the intermediate value. Possibly, but the trick would be to figure out when to evaluate the values so that it would still behave the same as without inlining. I don't think the existing Param mechanism could do this without some additional help. maybe subject for ToDo? Regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] huia and moa versus old PG branches
The new buildfarm machines huia and moa aren't doing terribly well with the older PG branches. This isn't really those machines' fault; what I find after a bit of digging is that we just didn't have good support for 64-bit Solaris until relatively recently. In particular: * There was no 64-bit spinlock assembler code that worked with Sun's compiler until 8.2. The first attempt to support it was here: http://archives.postgresql.org/pgsql-committers/2005-12/msg00507.php although that got whacked around quite a bit before 8.2 final. * gcc builds didn't fully work in 64-bit Solaris either until 8.3: http://archives.postgresql.org/pgsql-committers/2007-01/msg00154.php Before that patch, contrib didn't build because pgcrypto needs BYTE_ORDER to be defined. huia, which is claimed on the buildfarm dashboard to be using Sun Studio but is actually using gcc, thus fails at the contrib make stage before 8.3. moa, which is claimed on the buildfarm dashboard to be using gcc but is actually using cc, hits the spinlock problem in 8.0 and 8.1 and the BYTE_ORDER problem in 8.2. That BYTE_ORDER patch is pretty small and safe, so I think it would be reasonable to back-patch it into 8.2 so that we have a uniform story that 64-bit Solaris is supported in 8.2 and up. The spinlock changes were significantly more invasive, so my feeling is we should not try to back-patch them, but just turn off moa for pre-8.2 branches. Also, although moa is actually green for 8.3, it's showing an initdb failure in 8.4 and up (cache lookup failed for type 0 while processing system_views.sql). I'm betting this is some sort of over-aggressive-optimization problem, but it's hard to tell much from the buildfarm logs. Could you look into that and find out exactly where it's failing? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] upcoming wraps
Apologies if this has already been covered, but do we have dates yet for the next (and in the case of 7.4 and 8.0, final) set of point releases? And what about 9.1alpha1? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Wed, Aug 25, 2010 at 2:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 25, 2010 at 1:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: The fact that the file was modified twice after being removed at rev 2.88 seems really wacko. Are you sure that's not contributing to what we're seeing here? Yeah, that was discussed in the earlier git-conversion thread that I pointed to. We never did figure out how that happened, though I speculated it might have been due to weirdness in Vadim's local checkout. Is it possible to just delete those two revisions from the CVS repository, and if so would it help? We certainly don't need 'em. cvs admin -o ? Magnus, is this something that you can try? Prune those could of wonky revisions after the delete and before the re-add prior to running the conversion, and see how that comes out? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] upcoming wraps
Robert Haas robertmh...@gmail.com writes: Apologies if this has already been covered, but do we have dates yet for the next (and in the case of 7.4 and 8.0, final) set of point releases? No. And what about 9.1alpha1? Peter muttered something about doing that this week. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
I wrote: what I find after a bit of digging is that we just didn't have good support for 64-bit Solaris until relatively recently. In particular: BTW, just for the record: it's Solaris on 64-bit Intel that's at issue. 64-bit Sparc support goes way back, as evidenced by the fact that protosciurus and castoroides are green back to 8.0. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
