On Wed, 21 Feb 2018 13:33:18 +0100 Alexander Farber <alexander.farber@xxxxxxxxx> wrote: > Hi Martin - > > On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore <martin.moore@xxxxxxxxxxx> > wrote: > > > I’m no expert but I’d think it unlikely an index would be considered for a > > table with only 100 rows in. Also I’m pretty sure only one index per table > > is used, so you’d want to put state1 and state2 in one index. > > I hope to have more records in the words_games table later when my game is > launched (currently in soft launch/beta)..... To elaborate+clarify Martin's comments. The index will not be used while the table is small because it's actually slower to process an index than it is to just read the entire table. However, as the table gets more rows, these timings will reverse and Postgres will start using the indexes. It's probably best to just create them even though the table is small. The performance improvement you'll get when the table grows will be well worth it, and it avoids the problem of trying to remember to create it later. However, Martin's other comment about only using a single index is incorrect. Postgres can use multiple indexes per query, so it's often good practace to put indexes on every column that might ever be used in a WHERE clause. That being said, if you'll always use a WHERE on both state1 and state2, it will be even more performant to create a multicolumn index on those two columns instead of two single-column indexes. Even though Postgres _can_ use multiple indexes, using a single index is faster if it's available. -- Bill Moran <wmoran@xxxxxxxxxxxxxxxxx>
