[gnome-db] postgresql provider performance

[Mark Johnson <mrj001 shaw ca>]

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