GNOME Bugzilla – Bug 513149
Postgres provider inefficiency
Last modified: 2008-11-17 10:17:53 UTC
am using libgda 3.1.2, and postgresql 8.2.6. I have been testing the (development) gda backend for gnucash. The performance with postgresql has been very slow. Logging of the queries sent by libgda to postgresql showed that for each query issued by gnucash to libgda, MANY queries were issued by libgda to postgresql. The following is an excerpt from the log file: gnucash_qif_dbLOG: statement: SELECT a.attname, t.typname, a.atttypmod, t.typlen, a.attnotnull, pg_get_expr (d.adbin, c.oid), a.attnum FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = c.oid) FULL JOIN pg_catalog.pg_attrdef d ON (a.attnum = d.adnum AND d.adrelid=c.oid) LEFT JOIN pg_catalog.pg_type t ON (t.o id = a.atttypid) WHERE c.relname = 'slots' AND pg_catalog.pg_table_is_visible (c.oid) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY 7 gnucash_qif_dbLOG: statement: SELECT i.indkey, i.indisprimary, i.indisunique FROM pg_catalog.pg_class c, pg_cata log.pg_class c2, pg_catalog.pg_index i WHERE c.relname = 'slots' AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND pg_catalog.pg_table_is_visible(c.oid) AND i.indkey [0] <> 0 gnucash_qif_dbLOG: statement: SELECT o.conkey, o.confkey, fc.relname FROM pg_catalog.pg_class c INNER JOIN pg_ca talog.pg_constraint o ON (o.conrelid = c.oid) LEFT JOIN pg_catalog.pg_class fc ON (fc.oid=o.confrelid) WHERE c.relname = 'slots' AND contype = 'f' AND pg_catalog.pg_table_is_visible (c.oid) AND pg_catalog.pg_table_is_visible (fc.oid) gnucash_qif_dbLOG: execute gda_query_prep_stm: SELECT t1.* FROM slots AS t1 WHERE t1.obj_guid='4cbf958a005bee408d13aef08914e1c9' gnucash_qif_dbLOG: statement: DEALLOCATE gda_query_prep_stm gnucash_qif_dbLOG: statement: SELECT c.relname FROM pg_catalog.pg_class c WHERE c.relkind = 'r' AND c.relnatts = '11' AND 'slot_id' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'obj_guid' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'name' IN (SELECT a.attname FRO M pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'slot_type' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'int64_val' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'string_val' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AN D 'double_val' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'timespec_val' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'guid_val' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) AND 'numeric_val_num' IN (SELECT a.attname FROM pg_catal og.pg_attribute a WHERE a.attrelid = c.oid) AND 'numeric_val_denom' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid) gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'slot_id' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'slot_id' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'obj_guid' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'obj_guid' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'name' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'name' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'slot_type' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'slot_type' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'int64_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'int64_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'string_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'string_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'double_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'double_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'timespec_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'timespec_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'guid_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'guid_val' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'numeric_val_num' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'numeric_val_num' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'numeric_val_denom' gnucash_qif_dbLOG: statement: SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'u' and c2.conkey[1] = a.attnum and a.attname = 'numeric_val_denom' The above excerpt is from one query issued by gnucash. I can't be absolutely certain where one query ends and the next begins, but this is one "cycle" from the log file, and the queries of the form "SELECT 1 FROM ...." do definitely occur after the prepared statement is executed. First, it looks like the very large number of queries issued by libgda for one query from the client is responsible for a significant loss of performance. Second, what is the point of all the "Select 1..." queries after the "deallocate"? There are two of them for every column in the table referenced by the prepared statement (created by the postgres provider's implementation of gda_query_execute). They do not produce meaningful results when executed in PostgreSQL's psql client. The where condition appears to be trying to check for unique and primary key constraints. However, the "1" in the select fields is a constant! These queries don't return even a single row! Here is a transcript of a psql session showing that: issue001_db=# \d+ slots Table "public.slots" Column | Type | Modifiers | Description -------------------+------------------------+-----------+------------- slot_id | integer | not null | obj_guid | character(32) | not null | name | character varying(500) | not null | slot_type | integer | not null | int64_val | bigint | | string_val | character varying(500) | | double_val | bigint | | timespec_val | date | | guid_val | character(32) | | numeric_val_num | bigint | | numeric_val_denom | bigint | | Indexes: "slots_pkey" PRIMARY KEY, btree (slot_id) Has OIDs: no issue001_db=# SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 'P' and c2.conkey[1] = a.attnum and a.attname = 'slot_id'; ?column? ---------- (0 rows) There is not a single row returned by this query. 'slot-id' is the primary key column, which seems to be something this query might be looking for. Does this possibly behave differently in an older version of PostgreSQL?
Could you please check if this is still a problem with svn trunk?
My second point in the original bug was addressed and fixed in Bug513543, and need not be considered further here.
Great. But could you still please check with svn trunk?
svn trunk 3078 did not compile for me earlier. I haven't yet had a chance to get back to it.
Gnucash-gda depends upon libgda-3.0. It will take me a while longer to test this as I will have to write a small custom project.
Tested with svn trunk 3091. This is no longer occurring. PostgreSQL is only logging the call to the prepared statement. My test program executed the GdaStatement twice (with different parameters), and dumped the returned model between calls.
Thanks, Mark. That's good to know. Keeping this open because I want to check the latest libgda 3.1 release and do a new release of that if necessary.
Closing because I am not likely to do more work on the 3.1/3.2 branch.