[SQL] Why are these queries so different in time?
Hi, I have a question about performance querying a 7.4 database. The orginal generated query was SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._product _product7 ON (_compound0.object_id = _product7.compound) LEFT JOIN amaze._database_object _database_object11 ON (_product7.reaction = _database_object11.object_id) LEFT JOIN amaze._educt _educt2 ON (_compound0.object_id = _educt2.compound) LEFT JOIN amaze._database_object _database_object6 ON (_educt2.reaction = _database_object6.object_id) WHERE ( _database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA + 3-Methyl-2-oxobutanoate + H2O' OR _database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA + 3-Methyl-2-oxobutanoate + H2O' ); This on take a huge time to perform, which may come to a timeout on the front-end application that uses the database. So, I decided to modify manually the query like this: SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._product _product7 ON (_compound0.object_id = _product7.compound) LEFT JOIN amaze._database_object _database_object11 ON (_product7.reaction = _database_object11.object_id) WHERE ( _database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA + 3-Methyl-2-oxobutanoate + H2O' ) UNION SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM amaze._compound _compound0 LEFT JOIN amaze._educt _educt2 ON (_compound0.object_id = _educt2.compound) LEFT JOIN amaze._database_object _database_object6 ON (_educt2.reaction = _database_object6.object_id) WHERE ( _database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA + 3-Methyl-2-oxobutanoate + H2O' ) This should give the same result set, but it's really faster than the previous one, more than one thousand time faster. Is there a reason for this huge difference of performance? Thanks in advance. -- Olivier Hubaut North Bears Team SCMBB - ULB ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] calling EXECUTE on any exception
Hi all: I want to call some FUNCTION , let say exception_hanler(Exception_code) for any exception in plsql functions. I give some pseudo code to explain myself better: CREATE FUNCION something(...) returns ... AS ' ... EXCEPTION WHEN OTHER THEN EXECUTE exception_handler(Exception_code) ... ' and let exception_handler() function make all the work and returns some value for mi program...Now, the actual question...how do i get that error_code (or code_name, whatever i can get) generated in the something() function to be proccesed by the exception_handler() function? Im talking of 8.0 plsql language by the way. Thanks in advance! -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] calling EXECUTE on any exception
On Fri, Jul 29, 2005 at 10:36:52AM -0300, [EMAIL PROTECTED] wrote: > EXCEPTION > WHEN OTHER THEN > EXECUTE exception_handler(Exception_code) > > how do i get that error_code (or code_name, whatever i can get) > generated in the something() function to be proccesed by the > exception_handler() function? I don't think you can do this in released versions of PostgreSQL. In 8.1 you'll be able to use SQLSTATE to get the error code and SQLERRM to get the error message. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Why are these queries so different in time?
Olivier Hubaut wrote: Hi, I have a question about performance querying a 7.4 database. The orginal generated query was SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM (4 LEFT JOINS then a couple of WHERE conditions on 2 tables) This on take a huge time to perform, which may come to a timeout on the front-end application that uses the database. So, I decided to modify manually the query like this: SELECT DISTINCT _compound0.object_id AS "ObjectId" FROM (Two lots of 2 x Left-joins, unioned together) This should give the same result set, but it's really faster than the previous one, more than one thousand time faster. Is there a reason for this huge difference of performance? You're probably processing 1000 more rows in the first example. It's probably running the LEFT JOIN across all the tables then restricting the results in the WHERE. As it happens you're throwing away duplicates with DISTINCT and/or UNION anyway, so you never get to see the results. Try an EXPLAIN ANALYSE of the first example and see if the rows= parts indicate very large numbers of rows being processed. To make it faster I'd remove the LEFT JOINs, since your WHERE conditions seem to rule out the NULL cases anyway. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] calling EXECUTE on any exception
Oh...to bad...Thank you Michael! Did someone know if it can be acomplished in pypgsql? Thanks againg falks. Gerardo > On Fri, Jul 29, 2005 at 10:36:52AM -0300, [EMAIL PROTECTED] wrote: >> EXCEPTION >> WHEN OTHER THEN >> EXECUTE exception_handler(Exception_code) >> >> how do i get that error_code (or code_name, whatever i can get) >> generated in the something() function to be proccesed by the >> exception_handler() function? > > I don't think you can do this in released versions of PostgreSQL. > In 8.1 you'll be able to use SQLSTATE to get the error code and > SQLERRM to get the error message. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] CREATE TABLE AS SELECT
Offered up because I have no explanation, and curiosity overwhelms me: I was attempting to create a table from a SELECT statement against another table: create table foo as select a, f(b) from xxx; The function f() attempts to make a unique value based on its argument (it's actually a "username" constructor, making "jboes" out of "Jeff Boes"). The odd thing is that function f() also looks into the table "foo" to see if the value it's constructing is truly unique; if it is not, it tacks on a "1", "2", etc. until it gets a unique value. The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT" statement, the function never found duplicate values, so I ended up with f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE, then VOLATILE, without success. But if I changed to create the table first, and then do "INSERT INTO ... SELECT", the function worked properly. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] CREATE TABLE AS SELECT
JB> Offered up because I have no explanation, and curiosity overwhelms me: JB> I was attempting to create a table from a SELECT statement against JB> another table: JB> create table foo JB> as select JB> a, JB> f(b) JB> from xxx; In this command table foo populated after the select statement sucessfully finished. The function always runs on an empty foo table. JB> The function f() attempts to make a unique value based on its argument JB> (it's actually a "username" constructor, making "jboes" out of "Jeff JB> Boes"). The odd thing is that function f() also looks into the table JB> "foo" to see if the value it's constructing is truly unique; if it is JB> not, it tacks on a "1", "2", etc. until it gets a unique value. JB> The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT" JB> statement, the function never found duplicate values, so I ended up with JB> f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE, JB> then VOLATILE, without success. But if I changed to create the table JB> first, and then do "INSERT INTO ... SELECT", the function worked properly. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How to loop though an array plpgsql?
I need to loop though an input array, but can't figure out how to do it, the docs aren't really clear on this. Something like this: CREATE FUNCTION update (_id INTEGER[]) RETURNS VOID AS $$ BEGIN FOR i IN SELECT _id LOOP INSERT INTO table VALUES (_id[i]); END LOOP; END; $$ LANGUAGE plpgsql; Anyone know the correct loop syntax? schu ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] bug in information_schema?
I noticed that it seemed a bit slow to query information_schema.view_column_usage. As I look at the code in information_schema.sql, I'm not sure why pg_user is referenced twice (once without an alias). It looks like we can take out the first pg_user and remove the DISTINCT keyword and this improves the efficiency significantly. It seems to return the same result but in half the time. Anyone see a problem with this? (The same problem may also be in view_table_usage but I haven't done any testing there yet.) Code from information_schema.sql: CREATE VIEW view_column_usage AS SELECT DISTINCT CAST(current_database() AS sql_identifier) AS view_catalog, CAST(nv.nspname AS sql_identifier) AS view_schema, CAST(v.relname AS sql_identifier) AS view_name, CAST(current_database() AS sql_identifier) AS table_catalog, CAST(nt.nspname AS sql_identifier) AS table_schema, CAST(t.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv, pg_depend dt, pg_class t, pg_namespace nt, pg_attribute a, pg_user u ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
