Re: [gnome-db] postgresql provider performance

["Vivien Malerba" <vmalerba gmail com>]

On Jan 30, 2008 9:35 AM, Mark Johnson <mrj001 shaw ca> wrote:
> 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!

It seems the "SELECT 1..." query is sent whenever
gda_data_model_describe_column() is called, and it can be optimized to
be called only once...

However, I'm in the process of modifying the way database providers
are implemented so I'll do a full review of the PostgreSQL's provider
implementation in the near future and won't work on it before that
except if you really need it.

Also, my understanding is that you are trying to make the Libgda
backend of Gnucash working correctly, if I'm wrong, forget the rest of
this paragraph. As I'm currently working on the V4 version of Libgda,
I've started to introduce some API changes to make the library easier
to use and understand and easier to implement providers, but with some
API changes, so I would suggest that you work on that backend with the
still-unreleased V4 version which is already useable to avoid having
to make yet another port to V4.

>
> Should I file a bug against the postgresql provider?

Yes, please, so I can keep a reminder of that specific problem.

Regards,

Vivien