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

On 17 August 2010 13:45, Thom Brown <thom(at)linux(dot)com> wrote:
> On 17 August 2010 04:05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Andy <angelflow(at)yahoo(dot)com> writes:
>>> 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?
>>
>> If I understood the original report correctly, they were complaining
>> mostly about index size, so a table without indexes certainly isn't
>> a real helpful comparison.
>
> Yeah, I did attempt to create a full text GIN index on that last
> night, but it was taking ages and it was getting late, so abandoned
> it.  If you're interested, I set up one on MySQL's version (MyISAM of
> course) and it was around 108 MB.  The problem is, if PostgreSQL's
> index was, say, 600 MB, it might still not be fair to compare it since
> they make not really be equivalent.
>
> But those slides leave a lot of important information out.  And even
> if it clearly explained everything in detail, they're talking about
> 7.4 and 8.0.  The world has changed since then.
>
Okay, I've left the creation of 2 full text indexes, one using GIN and
another using GiST. GIN comes up with 72 MB and GiST 21 MB.

But again, this is all rather synthetic and the data I've used
contains duplicate content. As for VACUUM performance hits, this has
changed since 8.0 too. 8.2 came with more efficient index VACUUMing.
8.3 introduced Heap-Only Tuples which allow dead tuples to be reused.
And VACUUM is also tunable in the config.

--
Thom Brown
Registered Linux user: #516935