RE: PostgreSQL and mySQL database size question - Mailing list pgsql-general
From | Fred Ingham |
---|---|
Subject | RE: PostgreSQL and mySQL database size question |
Date | |
Msg-id | PNEJKLGAKBJECOKMAOPHGEDOCPAA.ingham@erols.com Whole thread Raw |
In response to | Re: PostgreSQL and mySQL database size question (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: PostgreSQL and mySQL database size question
|
List | 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
pgsql-general by date: