Skip site navigation (1) Skip section navigation (2)

RE: PostgreSQL and mySQL database size question

From: "Fred Ingham" <ingham(at)erols(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: RE: PostgreSQL and mySQL database size question
Date: 2001-04-30 18:40:32
Message-ID: PNEJKLGAKBJECOKMAOPHGEDOCPAA.ingham@erols.com (view raw or flat)
Thread:
Lists: pgsql-general
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



In response to

Responses

pgsql-general by date

Next:From: Michelle MurrainDate: 2001-04-30 18:44:53
Subject: Re: Locking a database
Previous:From: Jose NorbertoDate: 2001-04-30 18:37:54
Subject: Locking a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group