Re: moving from MySQL to pgsql

On Wed, Oct 10, 2012 at 2:20 AM, Vineet Deodhar <vineet(dot)deodhar(at)gmail(dot)com>wrote:

> On Wed, Oct 10, 2012 at 2:38 PM, Chris Travers <chris(dot)travers(at)gmail(dot)com>wrote:
>
>>
>>
>> On Wed, Oct 10, 2012 at 1:47 AM, Vineet Deodhar <vineet(dot)deodhar(at)gmail(dot)com
>> > wrote: PostgreSQL has an excellent optimizer and the on-disk layout is
>> completely different. This will dwarf any changes due to threads vs
>> queries.
>>
>
>
>> However be prepared to rethink your indexing strategies.
>>
>> Best Wishes,
>> Chris Travers
>>
>
>
> Thanks Chris.
> I didn't understand by what do you mean by "be prepared to rethink your
> indexing strategies."
>
> In MySQL, I have created indexes, Unique indexes, complex or multi-field
> indexes, etc.
> In what way should I re-consider the indexing?
>
> In InnoDB your tables are basically primary key indexes with the rest of
the row data attached. For this reason a sequential scan is *slow* since
it cannot traverse the table in physical order. In PostgreSQL tables are
indexed paged heaps and there is essentially no difference between a UNIQUE
index on not null columns and a primary key.

What this means is that in MySQL/InnoDB more indexes are almost always
better, because a sequential scan is always very slow. In PostgreSQL,
sequential scans are pretty fast but primary key lookups are a little
slower. Consequently on PostgreSQL you may want to reduce the number of
non-unique indexes at first and add back as necessary.