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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, April 30, 2001 1:40 AM
To: ingham(at)erols(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question

"Fred Ingham" <ingham(at)erols(dot)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