[SQL] report generation from table.
Hi All, I have been searching for, Preparing report from a huge table. Queries am executing now are, SELECT count(*) from HUGE_TBL where cond1, cond2; SELECT count(*) from HUGE_TBL where cond1, cond3; --- like this i have different conditions(10-15 different things ) and different amount of conditions ( 2 - 5 condition ). As that is a very huge table, it took time to execute each query ( 2 min ). And finally it takes 15 times that ( 30 min ). Is there anyway to do the above report efficiently ? Such executing the query only once, and getting all different outputs required ? or fetching result from some cache ? Or i dont have any other way ?!
Re: [SQL] report generation from table.
In response to sathiya psql : > Hi All, > > I have been searching for, Preparing report from a huge table. > > Queries am executing now are, > SELECT count(*) from HUGE_TBL where cond1, cond2; > SELECT count(*) from HUGE_TBL where cond1, cond3; > --- like this i have different conditions(10-15 different things ) and > different amount of conditions ( 2 - 5 condition ). > > As that is a very huge table, it took time to execute each query ( 2 min ). > And > finally it takes 15 times that ( 30 min ). Is there anyway to do the above > report efficiently ? > > Such executing the query only once, and getting all different outputs required > ? or fetching result from some cache ? select sum(case when and then 1 else 0 end) as query1, sum(case when and then 1 else 0 end) as query2, ... from table; It forces a whole seq. scan, but only once. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] report generation from table.
A. Kretschmer wrote: > In response to sathiya psql : >> Hi All, >> >> I have been searching for, Preparing report from a huge table. >> >> Queries am executing now are, >> SELECT count(*) from HUGE_TBL where cond1, cond2; >> SELECT count(*) from HUGE_TBL where cond1, cond3; >> --- like this i have different conditions(10-15 different things ) and >> different amount of conditions ( 2 - 5 condition ). >> >> As that is a very huge table, it took time to execute each query ( 2 min ). >> And >> finally it takes 15 times that ( 30 min ). Is there anyway to do the above >> report efficiently ? >> >> Such executing the query only once, and getting all different outputs >> required >> ? or fetching result from some cache ? > > select sum(case when and then 1 else 0 end) as query1, > sum(case when and then 1 else 0 end) as query2, ... from > table; > > > It forces a whole seq. scan, but only once. > > > Regards, Andreas Probably should check (analyse) the various queries separately to see if any of them scan the huge table. If one does scan the table, then give Andreas's plan should be fine. However, it's possible that your conditions are all hitting indexes and not scanning, in which case you may be better off as is. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] @@Error equivalent in Postgresql
Hi, I am in the process of migrating our database from MS Server 2000 to Postgres. I have a bunch of stored procs which i have to modify the syntax so they work in postgresql. My ? is is there an equivalent for the @@Error function in T-SQL for postgres: The stored proc i am converting is: ALTER PROCEDURE [dbo].[AuditAccounts] @ReturnValueint output AS SET NOCOUNT ON select * from AdminAccts full join AmAccts on adm_acc_AccountNo = am_acc_AccountNo where adm_acc_AccountNo is null or am_acc_AccountNo is null Set @ReturnValue = @@Error I have wriiten the postgres function as follows : CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, am_acc_AccountNo character varying); CREATE FUNCTION dint_AuditAccounts( ) RETURNS SETOF AuditAccount AS $BODY$ BEGIN RETURN QUERY select * from "AdminAccounts" full join "AmAccounts" on "adm_acc_AccountNo" = "am_acc_AccountNo" where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 10; How do i implement exception handling in this case, if i want the function to report back successful execution or failure just like the @@Error function does in T-SQL? -- View this message in context: http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] @@Error equivalent in Postgresql
2009/10/21 maboyz : > > Hi, > > I am in the process of migrating our database from MS Server 2000 to > Postgres. I have a bunch of stored procs which i have to modify the syntax > so they work in postgresql. My ? is is there an equivalent for the @@Error > function in T-SQL for postgres: The stored proc i am converting is: > > ALTER PROCEDURE [dbo].[AuditAccounts] > > �...@returnvalue int output > AS > > SET NOCOUNT ON > > select * from > AdminAccts full join AmAccts > on adm_acc_AccountNo = am_acc_AccountNo > where > adm_acc_AccountNo is null > or am_acc_AccountNo is null > > Set @ReturnValue = @@Error > > I have wriiten the postgres function as follows : > > CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, > am_acc_AccountNo character varying); > CREATE FUNCTION dint_AuditAccounts( ) > RETURNS SETOF AuditAccount AS > $BODY$ > BEGIN > RETURN QUERY > select * from "AdminAccounts" > full join "AmAccounts" > on "adm_acc_AccountNo" = "am_acc_AccountNo" > where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; > > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100 > ROWS 10; > > How do i implement exception handling in this case, if i want the function > to report back successful execution or failure just like the @@Error > function does in T-SQL? > -- Hello PostgreSQL has different model of error processing than MSSQL. When any exception is raised, then simply is raised and not silently ignored like in T-SQL. You can catch exception. See http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Then you can use SQLSTATE and SQLERRM variables. p.s. For similar function like your function use sql language. It could be more effective: CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character varying, OUT am_acc_AccountNo character varying) RETURNS SETOF record AS $BODY$ select * from "AdminAccounts" full join "AmAccounts" on "adm_acc_AccountNo" = "am_acc_AccountNo" where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; $BODY$ LANGUAGE sql; You don't need set flags because planner see inside sql functions. Regards Pavel Stehule > View this message in context: > http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > 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] @@Error equivalent in Postgresql
Thanks for the response Pavel. So does this mean i will have to make the @ReturnValue an OUT parameter too??? am a bit confused here, i guess what i am driving at is, i see where you are going with the altered function you suggeted but its fitting the exception handling into the grand scheme of things so i can be able to manipulate it in the code just like you wd use the @returnValue = @@Error. Thanks Pavel Stehule wrote: > > 2009/10/21 maboyz : >> >> Hi, >> >> I am in the process of migrating our database from MS Server 2000 to >> Postgres. I have a bunch of stored procs which i have to modify the >> syntax >> so they work in postgresql. My ? is is there an equivalent for the >> @@Error >> function in T-SQL for postgres: The stored proc i am converting is: >> >> ALTER PROCEDURE [dbo].[AuditAccounts] >> >> �...@returnvalue int output >> AS >> >> SET NOCOUNT ON >> >> select * from >> AdminAccts full join AmAccts >> on adm_acc_AccountNo = am_acc_AccountNo >> where >> adm_acc_AccountNo is null >> or am_acc_AccountNo is null >> >> Set @ReturnValue = @@Error >> >> I have wriiten the postgres function as follows : >> >> CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, >> am_acc_AccountNo character varying); >> CREATE FUNCTION dint_AuditAccounts( ) >> RETURNS SETOF AuditAccount AS >> $BODY$ >> BEGIN >> RETURN QUERY >> select * from "AdminAccounts" >> full join "AmAccounts" >> on "adm_acc_AccountNo" = "am_acc_AccountNo" >> where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; >> >> END; >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE >> COST 100 >> ROWS 10; >> >> How do i implement exception handling in this case, if i want the >> function >> to report back successful execution or failure just like the @@Error >> function does in T-SQL? >> -- > > Hello > > PostgreSQL has different model of error processing than MSSQL. When > any exception is raised, then simply is raised and not silently > ignored like in T-SQL. You can catch exception. See > > http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > Then you can use SQLSTATE and SQLERRM variables. > > p.s. For similar function like your function use sql language. It > could be more effective: > > CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character > varying, >OUT > am_acc_AccountNo character varying) > RETURNS SETOF record AS > $BODY$ > select * from "AdminAccounts" > full join "AmAccounts" > on "adm_acc_AccountNo" = "am_acc_AccountNo" > where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; > $BODY$ > LANGUAGE sql; > > You don't need set flags because planner see inside sql functions. > > Regards > Pavel Stehule > >> View this message in context: >> http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.html >> Sent from the PostgreSQL - sql mailing list archive at Nabble.com. >> >> >> -- >> 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 > > -- View this message in context: http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25998338.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] @@Error equivalent in Postgresql
2009/10/21 maboyz : > > Thanks for the response Pavel. So does this mean i will have to make the > @ReturnValue an OUT parameter too??? am a bit confused here, i guess what i > am driving at is, i see where you are going with the altered function you > suggeted but its fitting the exception handling into the grand scheme of > things so i can be able to manipulate it in the code just like you wd use > the @returnValue = @@Error. Thanks yes, if you like to return state, then you have to mark it as OUT. It's better if you are drop your knowledge from T-SQL and start from zero. PL/pgSQL is modern language based on Ada language. Mainly - it is too different than T-SQL stored procedures programming - but is near to Oracle's programming. It is exception based. So the programming based on returning state codes is very obsolete, and little bit difficult. You can emulate, but any protected block creates inner transaction and this should negative effect on speed - and it are some lines more. http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 Pavel > > Pavel Stehule wrote: >> >> 2009/10/21 maboyz : >>> >>> Hi, >>> >>> I am in the process of migrating our database from MS Server 2000 to >>> Postgres. I have a bunch of stored procs which i have to modify the >>> syntax >>> so they work in postgresql. My ? is is there an equivalent for the >>> @@Error >>> function in T-SQL for postgres: The stored proc i am converting is: >>> >>> ALTER PROCEDURE [dbo].[AuditAccounts] >>> >>> �...@returnvalue int output >>> AS >>> >>> SET NOCOUNT ON >>> >>> select * from >>> AdminAccts full join AmAccts >>> on adm_acc_AccountNo = am_acc_AccountNo >>> where >>> adm_acc_AccountNo is null >>> or am_acc_AccountNo is null >>> >>> Set @ReturnValue = @@Error >>> >>> I have wriiten the postgres function as follows : >>> >>> CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, >>> am_acc_AccountNo character varying); >>> CREATE FUNCTION dint_AuditAccounts( ) >>> RETURNS SETOF AuditAccount AS >>> $BODY$ >>> BEGIN >>> RETURN QUERY >>> select * from "AdminAccounts" >>> full join "AmAccounts" >>> on "adm_acc_AccountNo" = "am_acc_AccountNo" >>> where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; >>> >>> END; >>> $BODY$ >>> LANGUAGE 'plpgsql' VOLATILE >>> COST 100 >>> ROWS 10; >>> >>> How do i implement exception handling in this case, if i want the >>> function >>> to report back successful execution or failure just like the @@Error >>> function does in T-SQL? >>> -- >> >> Hello >> >> PostgreSQL has different model of error processing than MSSQL. When >> any exception is raised, then simply is raised and not silently >> ignored like in T-SQL. You can catch exception. See >> >> http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> >> Then you can use SQLSTATE and SQLERRM variables. >> >> p.s. For similar function like your function use sql language. It >> could be more effective: >> >> CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character >> varying, >> OUT >> am_acc_AccountNo character varying) >> RETURNS SETOF record AS >> $BODY$ >> select * from "AdminAccounts" >> full join "AmAccounts" >> on "adm_acc_AccountNo" = "am_acc_AccountNo" >> where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; >> $BODY$ >> LANGUAGE sql; >> >> You don't need set flags because planner see inside sql functions. >> >> Regards >> Pavel Stehule >> >>> View this message in context: >>> http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.html >>> Sent from the PostgreSQL - sql mailing list archive at Nabble.com. >>> >>> >>> -- >>> 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 >> >> > > -- > View this message in context: > http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25998338.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > 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
[SQL] Planner behaviour
Hi, I have a table with emails. I want to search this column with wildcards. To make a wildcard like this possible: "*[email protected]" , I use this query: SELECT * FROM users WHERE lower(reverse_str(email))) LIKE 'moc.niamode...@%' ORDER BY email (I use reverse_str as the index only gets queried when the constant part of the string matched by LIKE is at the beginning of the string) to speed things up I have a index on "lower(reverse_str(email))" Everything works, the index is queried Now the strange part: As soos as I add "LIMIT 10" to the query: SELECT * FROM users WHERE reverse_str(email)) LIKE 'moc.niamode...@%' ORDER BY email LIMIT 10 the database does not use the "reverse_str(email)" index, but just the "email" index, and the query takes endless. Why? What can I do? Plan with "LIMIT" : explain select email FROM book_users WHERE lower(reverse_str(email)) LIKE 'moc.niamode...@%' order by email limit 10; QUERY PLAN - Limit (cost=0.00..8094.69 rows=10 width=23) -> Index Scan using book_users_email_key on book_users (cost=0.00..16868526.16 rows=20839 width=23) Filter: (lower(reverse_str((email)::text)) ~~ 'moc.niamode...@%'::text) (3 rows) Plan without "LIMIT": explain select email FROM book_users WHERE lower(reverse_str(email)) LIKE 'moc.niamode...@%' order by email; QUERY PLAN -- Sort (cost=70639.69..70691.79 rows=20839 width=23) Sort Key: email -> Bitmap Heap Scan on book_users (cost=635.19..69144.81 rows=20839 width=23) Filter: (lower(reverse_str((email)::text)) ~~ 'moc.niamode...@%'::text) -> Bitmap Index Scan on book_users_lower_rev_email_key (cost=0.00..629.98 rows=20839 width=0) Index Cond: ((lower(reverse_str((email)::text)) >= 'moc.niamodemos@'::text) AND (lower(reverse_str((email)::text)) < 'moc.niamodemosA'::text)) (6 rows) With LIMIT it takes endless, without only a fraction of a second. PS: with LIMIT 100 the behavior switches to the same behavior as without limit Thank you very much Sebastian -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
