RE: PostgreSQL and mySQL database size question

Tom,

Again, thank you for your quick and knowledgeable assistance.

After dropping and recreating the indexes I have (in KB):

old new
PostgreSQL pinndex_seq 8 8
pinndx 7,856 7,856
pinndx_pkey 6,984 6,984 (did not recreate)
parent_ndx 6,952 2,040
tagpath_ndx 5,552 2,040
tagname_ndx 5,560 2,040
atrname_ndx 5,696 2,040
pinnum_ndx 6,160 2,040
nvalue_ndx 5,832 2,040
value_ndx 6,424 2,432
57,024 29,520

So, it appears that I am running into the pathological case. Most of the
index values are NOT random, the primary key will be ever increasing (via
the sequence), the parent, tagpath, tagname, atrname, and pinnum colums
contain many repeated values, only the nvalue and value columns are truly
random.

I do not import or bulk load the values, they are sent in via JDBC in an
asynchronous manner (via a server socket). It would be very difficult (not
impossible) for me to periodically drop and recreate the indexes (although,
I must say it was very fast doing so).

I do in fact need all of the indexes. Without going into detail, the
application is 'indexing' millions of XML documents, the tagname, tagpath,
atrname columns are for the XML document's element name, the path leading up
to the element, and the element's attributes, the parent column associates
an element with its parent, etc. In short, they are all needed for
acceptable performance querying and retrieving values from the database.

With respect to mySQL, I did verify that mySQL did in fact have all of the
indexes I created and that they were saved in a single file (the *.MYI).
Here is the result of the 'show index' command in mySQL.

mysql> show index from pinndx;
+--------+------------+------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Comment |
+--------+------------+------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| pinndx | 0 | PRIMARY | 1 | pinndxnum | A
| 92488 | NULL | NULL | |
| pinndx | 1 | parentndx | 1 | parent | A
| NULL | NULL | NULL | |
| pinndx | 1 | tagpathndx | 1 | tagpath | A
| NULL | NULL | NULL | |
| pinndx | 1 | tagnamendx | 1 | tagname | A
| NULL | NULL | NULL | |
| pinndx | 1 | atrnamendx | 1 | atrname | A
| NULL | NULL | NULL | |
| pinndx | 1 | pinnumndx | 1 | pinnum | A
| NULL | NULL | NULL | |
| pinndx | 1 | nvaluendx | 1 | nvalue | A
| NULL | NULL | NULL | |
| pinndx | 1 | valuendx | 1 | value | A
| NULL | 30 | NULL | |
+--------+------------+------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
8 rows in set (0.07 sec)

Fred