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

On 02/21/2018 01:33 PM, Alexander Farber wrote:
> Hi Martin -
>
> On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore <martin(dot)moore(at)avbrief(dot)com
> <mailto: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).....
>

I do strongly recommend generating a representative amount of data as
part of testing, and making sure all the indexes are there before going
live. Otherwise you'll be playing whack-a-mole over a long period of
time, depending on how fast the individual tables grow.

Either setup a script that generates data through the application, or
generate synthetic data in some other way.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services