Re: PostgreSQL and mySQL database size question

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

Hm. All but value_ndx are indexes on integer columns, so the keys are
only 4 bytes. The index tuple overhead will be either 12 or 16 bytes
per entry depending on whether your hardware has any datatypes that
require 8-byte alignment (I think not on PCs --- what is MAXIMUM_ALIGNOF
in your src/include/config.h?). 16 bytes times 92000 entries is only
about a meg and a half; even allowing for the traditional 70% fill
factor of btrees, you shouldn't see more than a couple meg per index.

That assumes random loading of the index, however, and I think there may
be pathological cases where the indexes come out less dense after
initial load. Was there any particular order to the data values when
you imported them? If you drop any of these indexes and CREATE it
again, is the result noticeably smaller?

> 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).

I find it hard to believe that MySQL is storing nine indexes on a
92000-entry table in only 4Mb. Storing the keys alone would take 3.3Mb,
never mind making the keys point to anything. Are you sure that you've
accounted for all of their index storage?

> In any case, is there anyway to reduce the size of the PostgreSQL
> index tables (are there different indexing schemes)?

Do you actually *need* an index on each column? It seems highly
unlikely that each one of these indexes will pay its keep.

regards, tom lane