RE: PostgreSQL and mySQL database size question

Tom,

Thank very much for the query, it was extremely helpful.  Using your query,
here is what I found for one of the tables:

            File         Size (KB)
mySQL       pinndx.MYD     3,947
            pinndx.MYI     4,144
                         8,091

PostgreSQL  pinndex_seq      8
            pinndx       7,856
            pinndx_pkey  6,984
            parent_ndx   6,952
            tagpath_ndx  5,552
            tagname_ndx  5,560
            atrname_ndx  5,696
            pinnum_ndx   6,160
            nvalue_ndx   5,832
            value_ndx    6,424
                        57,024

Based on this information, I conclude that PostgreSQL is using significantly
more space for its indexes than mySQL (the .MYI file contains all of the
indexes on the pinndx table in mySQL).

That may also explain another item that has been troubling me, the
difference in insert/update speed between PostgreSQL and mySQL.  It seems to
take PostgreSQL significantly more time to insert into its pinndx table than
mySQL... perhaps this is due to the i/o of writing to all of the index
files?  In any case, is there anyway to reduce the size of the PostgreSQL
index tables (are there different indexing schemes)?

Fred

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, April 30, 2001 1:40 AM
To: ingham@erols.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question


"Fred Ingham" <ingham@erols.com> writes:
> and populated them both with the same data: PIN table with 1,740 tuples
and
> the PINNDX table with 92,488 tuples.  Looking at the number of files and
> size of the files, I get the following (after doing a vacuumdb with
> PostgreSQL):

> PostgreSQL
>   Files  109
>   Size   60.7MB

That seems excessive to me too --- but it's fair to ask exactly what you
were counting; does that include system catalogs, or just these two
tables and their indexes?  It would help to see an ls -lR of the $PGDATA
directory and a map of the table numbers; you can make the latter with
    select relfilenode, relname from pg_class order by relfilenode;

            regards, tom lane