Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

On Wed, 21 Feb 2018 13:33:18 +0100
Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:

> Hi Martin -
>
> On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore <martin(dot)moore(at)avbrief(dot)com>
> 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(at)potentialtech(dot)com>