[opencms-dev] opencms6 - postgresql8 and performance

[Andreas Haumer]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Alexander Kandzior schrieb:
| Sylvie,
|
| Thanks for this contribution.
|
| You did change quite a lot of lines in the script (revealed by diff to the
| current version). The old version was rather well tested and known to work
| with PostgreSQL 7/8.
|
| Is there someone else using PostgeSQL who can confirm that the new script
| also works as well as the old one?
|
I did a short test with OpenCMS 6.0.0 and a PostgreSQL 8.0.3
database:

*) Fresh install of OpenCMS 6.0.0
*) Complete install of all modules in the distribution
*) installed some example and demo sites made by myself
*) Moved some resources around
*) Deleted some resources
*) Created some resources
*) re-create search index
*) browse through sites in online and offline mode

I found no obviously broken things.

But after a short look at the SQL script I think it
should be cleaned up a little bit:

a) It would be nice if the SQL statements wouldn't be
~   shuffled around that much (compared to the original
~   script)

b) There is a (small) typo:

CREATE INDEX IDX_GROUPS_PERENTID
~        ON CMS_GROUPS (PARENT_GROUP_ID);

c) Several columns have more than one index of the same type.
~   I can't think of a situation where this would make sense.

Example:

opencms=# \d cms_backup_propertydef
~                       Tabelle »public.cms_backup_propertydef«
~      Spalte      |          Typ           | Attribute
- ------------------+------------------------+-----------
~ propertydef_id   | character varying(36)  | not null
~ propertydef_name | character varying(128) | not null
Indexe:
~    »cms_backup_propertydef_pkey« PRIMARY KEY, btree (propertydef_id)
~    »cms_backup_propertydef_propertydef_name_key« UNIQUE, btree (propertydef_name)
~    »idx_backup_propertydef_1« UNIQUE, btree (propertydef_id)
~    »idx_backup_propertydef_2« UNIQUE, btree (propertydef_name)

A short look at the database created by the new script reveals
the following indices which are doubled (or even tripled):

cms_backup_propertydef_pkey == idx_backup_propertydef_1
cms_backup_propertydef_propertydef_name_key == idx_backup_propertydef_2
cms_online_propertydef_pkey == idx_online_propertydef_1
cms_offline_properties_pkey == idx_offline_properties_pdef
idx_offline_resources_rt == idx_offline_resources_4
idx_online_resources_rt == idx_online_resources_4
idx_backup_resources_4 == idx_backup_resources_rid == idx_backup_resources_rt

(I might have missed some)

Also, IMHO there are now _many_ indices in the data model.
I haven't really analyzed the queries, but my gut feelings
tell me that some of the indices aren't necessary.

I would recommend to clean up the script at least from the
doubled and tripled indices before it is added to OpenCMS.

HTH

- - andreas

- --
Andreas Haumer                     | mailto:andreas at xss.co.at
*x Software + Systeme              | http://www.xss.co.at/
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDKa+QxJmyeGcXPhERAjRnAJ9n6z1dYHaMgSgbBRVNagTB/PlZfQCgsfTm
i30ke0lP8RBRISadoDFqyTs=
=LK7U
-----END PGP SIGNATURE-----