On Wed, Oct 10, 2012 at 2:20 AM, Vineet Deodhar
<vineet.deodhar@gmail.com> wrote:
On Wed, Oct 10, 2012 at 2:38 PM, Chris Travers
<chris.travers@gmail.com> wrote:
On Wed, Oct 10, 2012 at 1:47 AM, Vineet Deodhar
<vineet.deodhar@gmail.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.
