Re: Postgresql's table & index compared to that of MySQL

--- On Mon, 8/16/10, Thom Brown <thom(at)linux(dot)com> wrote:

> This is quite a crude and unrealistic test (as you'd need a
> set of
> real-world data), but just did a quick test using
> PostgreSQL 9.0 alpha
> 4 and MySQL .  I created a new database in both
> PostgreSQL and MySQL.
> Created the same table in both, and loaded identical data
> (50
> paragraphs of lorem ipsum) and got it to insert the table's
> contents
> back into itself until both reached 65,536 rows.  I
> also did a VACUUM
> in PostgreSQL and an OPTIMIZE TABLE in MySQL.
>
> PostgreSQL's table size shows 867 MB
> MySQL's table size as MyISAM shows 2,542 MB
> MySQL's table size as InnoDB shows: 3,576 MB
>
> Also bear in mind that MySQL's InnoDB engine doesn't
> support full text
> indexes, and when you can apply full text indexes, it only
> returns a
> result if it matches less than 50% of the total rows in the
> table.
>
> PostgreSQL provides GIN and GiST types of index which are
> used for
> full text searches, but off the top of my head I don't know
> if either
> is actually equivalent to MySQL's implementation.  I
> suspect they're
> quite different.  Hopefully someone more familiar with
> both system's
> full text search features can answer that.
>

Thanks for doing the test.

Your results of 867MB for Postgresql & 3,576 MB for InnoDB are surprising. Do you know why it is so much smaller for Postgresql? Are there any indexes?

Are all Postgresql indexes based on GIN & GiST? I'm not using the database for full text search, would I still be using GIN/GiST indexes, or would I be using the plain old B+ tree?