Re: [SQL] 8.4.1 distinct query WITHOUT order by
On Tue, Dec 22, 2009 at 12:11 AM, msi77 ms...@yandex.ru wrote: What are the ramifications of renaming the table (containing 8000 rows) and creating a view of the same name? View does not admit ORDER BY clause, at least, Standard does not. Postgres certainly allows it, but I don't think it will help in this case. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] short-cutting if sum()constant
Hi, I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
2009/12/22 Ivan Sergio Borgonovo m...@webthatworks.it Hi, Hi :-) I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? With plain SQL, no. With a user defined function in PL/PgSQL, yes. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [SQL] short-cutting if sum()constant
On Tue, 2009-12-22 at 13:35 +0100, Ivan Sergio Borgonovo wrote: Hi, I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? SELECT SUM(object_version) FROM date_x WHERE owner_id = 10100 HAVING SUM(object_version) 1000 ? -- OpenGroupware developer: awill...@whitemice.org http://whitemiceconsulting.blogspot.com/ OpenGroupare Cyrus IMAPd documenation @ http://docs.opengroupware.org/Members/whitemice/wmogag/file_view -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
In response to Ivan Sergio Borgonovo : Hi, I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? I think no. But you can create a new table with 2 columns: status (primary key) and a column for the sum of qty. And you need a TRIGGER: update this table for every insert, update and delete on your table t. Now you can ask _this_ new table if the sum(qty) your constant, this should work very fast. Just an idea... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
Does PG support CTE? You can try it. In response to Ivan Sergio Borgonovo : Развернуть Hi, I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? I think no. But you can create a new table with 2 columns: status (primary key) and a column for the sum of qty. And you need a TRIGGER: update this table for every insert, update and delete on your table t. Now you can ask _this_ new table if the sum(qty) your constant, this should work very fast. Just an idea... Andreas Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
On Tue, 22 Dec 2009 14:48:55 +0100 Filip Rembiałkowski plk.zu...@gmail.com wrote: With plain SQL, no. With a user defined function in PL/PgSQL, yes. thanks to all. I'm on 8.3 so no CTE. I was hoping there was some way to write it in plain SQL. I'm planning to wrap everything in a plpgsql function using cursors and then switch to WITH when I'll move to 8.4. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
msi77 ms...@yandex.ru wrote: Does PG support CTE? Since 8.4 yes. You can try it. Sorry, but i don't know how a CTE can help in this case, can you explain that? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
Sorry, but i don't know how a CTE can help in this case, can you explain I mean RECURSIVE CTE. You can check your condition on each iteration and stop execution when condition is false. Sergey msi77 ms...@yandex.ru wrote: Развернуть Does PG support CTE? Since 8.4 yes. Развернуть You can try it. Sorry, but i don't know how a CTE can help in this case, can you explain that? Andreas Здесь спама нет http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
Hello I found one ugly trick. You can multiply lines and SUM cons could be replaced limit clause: postgres=# select * from data; a --- 3 2 1 4 2 3 (6 rows) Then SELECT * FROM WHERE and stop when SUM(a) = n then postgres=# select generate_series(1,a) from data; generate_series - 1 2 3 1 2 1 1 2 3 4 1 2 1 2 3 So If I would to check if there are sum(a) = 10 then I can use LIMIT 10. If query returns ten rows, then result is true, else result is false select a, (a = generate_series(1,a))::int from data limit 12; -- stop after sum(a) = 12 postgres=# select sum(x) from (select 1 as x,(a = generate_series(1,a))::int from data limit 12) s; sum - 12 -- 12 is eq 12, so test is successful (1 row) Regards Pavel Stehule 2009/12/22 Ivan Sergio Borgonovo m...@webthatworks.it: Hi, I'd like to know if select sum(qty) from t where status=37; is constant. qty is always 0. Is there a way to skip examining further rows and return a result ASAP? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
On Wed, 23 Dec 2009 00:00:31 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Tue, 22 Dec 2009 20:47:18 +0100 Pavel Stehule pavel.steh...@gmail.com wrote: Hello I found one ugly trick. You can multiply lines and SUM cons could be replaced limit clause: The trick is really smart (and fun), kudos, really, it's always a pleasure to read your solutions, thanks. But as expected: as unexpected... test=# create or replace function tano(a int, out b int) ^^^ should be anything but a returns int as $$ declare row record; begin b :=0; for row in select a as _a from data where a0 loop b := row._a + b; if (b=a) then return; end if; end loop; return; end; $$ language plpgsql; Making it longer to better appreciate the difference: 1M rows where a [0,2] select * from tano((100)::int); b - 101 (1 row) Time: 1235.243 ms select sum(x) from (select 1 as x,(a = generate_series(1,a))::int from data limit 100) s; sum - 100 (1 row) Time: 1309.441 ms Being fair once you add the where clause to the generate_series version the difference in performance is negligible and saying that the plpgsql version is faster would require some more serious benchmarking. Surprised! If the generate_series can compete with the plpgsql for loop... why is the plpgsql version so slow? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
On Wed, 23 Dec 2009 01:09:40 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Wed, 23 Dec 2009 00:00:31 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Tue, 22 Dec 2009 20:47:18 +0100 Pavel Stehule pavel.steh...@gmail.com wrote: Hello I found one ugly trick. You can multiply lines and SUM cons could be replaced limit clause: The trick is really smart (and fun), kudos, really, it's always a pleasure to read your solutions, thanks. But as expected: as unexpected... As even more unexpected... when all row are 0 and most of them are equal to 1 the generate_series performs appreciably better (roughly 15% faster). And I think your version can be further optimised: select count(*) from (select (generate_series(1,a))::int from data limit 9000) s; This perform 30% faster. So what's so slow in the plpgsql version? Fortunately as expected when enough rows are 1 the for loop solution perform much better. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()constant
2009/12/23 Ivan Sergio Borgonovo m...@webthatworks.it: On Wed, 23 Dec 2009 01:09:40 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Wed, 23 Dec 2009 00:00:31 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Tue, 22 Dec 2009 20:47:18 +0100 Pavel Stehule pavel.steh...@gmail.com wrote: Hello I found one ugly trick. You can multiply lines and SUM cons could be replaced limit clause: The trick is really smart (and fun), kudos, really, it's always a pleasure to read your solutions, thanks. But as expected: as unexpected... As even more unexpected... when all row are 0 and most of them are equal to 1 the generate_series performs appreciably better (roughly 15% faster). And I think your version can be further optimised: select count(*) from (select (generate_series(1,a))::int from data limit 9000) s; This perform 30% faster. So what's so slow in the plpgsql version? don't forget - plpgsql is interpret - it is best as glue for SQL statement. I don't thing so plpgsql is slow - speed is similar to using buildin functionality. But I am sure, rewritening your function to C could help. If you need maximal speed. I thing, so there are other trick, I am not sure if it is faster. You can create own aggregate. In state function you can calculate and check state value. If it is over your limit, then you can raise exception. So if your query will be finished with custom exception, then sum(c) n is true. Regards Pavel Stehule Fortunately as expected when enough rows are 1 the for loop solution perform much better. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
