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

On 17 August 2010 16:00, Thom Brown <thom@linux.com> wrote:
> On 17 August 2010 13:45, Thom Brown <thom@linux.com> wrote:
>> On 17 August 2010 04:05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Andy <angelflow@yahoo.com> writes:
>>>> Your results of 867MB for Postgresql & 3,576 MB for InnoDB are surprising. Do you know why it is so much smaller
forPostgresql? 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.
>

Actually, if MySQL won't return anything which occurs in 50% or more
of the rows, and all the rows in my test were duplicates, what's it
spending 108 MB on if there's no full text query I can use which can
return results?

--
Thom Brown
Registered Linux user: #516935