I 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!
Should I file a bug against the postgresql provider?
Mark
_______________________________________________
gnome-db-list mailing list
[email protected]
http://mail.gnome.org/mailman/listinfo/gnome-db-list
