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

On 02/21/2018 05:00 PM, Bill Moran wrote:
> 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.
>

I call this practice "shotgun" and generally discourage people from
using it. It seems attractive, but not every where condition can be
evaluated using an index, and every index has maintenance overhead.

There are cases when it's somewhat reasonable (e.g. when you don't know
which columns will be referenced by WHERE conditions, and data ingestion
has lower priority than queries). But that doesn't seem to be the case
here - you know the WHERE conditions, and people are likely sending a
lot of inserts (and expecting low latency responses).

regards

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