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