Thread: Not sure if I should CREATE INDEX for text columns on which I plan tofilter later

On Wed, 21 Feb 2018 13:33:18 +0100
Alexander Farber <alexander.farber@gmail.com> wrote:

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

    
>    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.
    
   > -- 
   > Bill Moran <wmoran@potentialtech.com>
    
    

That's very useful to know!

Martin.

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

On 02/21/2018 05:00 PM, Bill Moran wrote:
> On Wed, 21 Feb 2018 13:33:18 +0100
> Alexander Farber <alexander.farber@gmail.com> wrote:
> 
>> Hi Martin -
>>
>> On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore <martin.moore@avbrief.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

On Thu, 22 Feb 2018 03:57:34 +0100
Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> 
> On 02/21/2018 05:00 PM, Bill Moran wrote:
> > On Wed, 21 Feb 2018 13:33:18 +0100
> > Alexander Farber <alexander.farber@gmail.com> wrote:
> > 
> >> Hi Martin -
> >>
> >> On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore <martin.moore@avbrief.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).

Can't _really_ disagree with anything you're saying there ... but I disagree
with the overall sentament for the following reasons:

1) Not everyone has easy access to experienced people like you and I. As a
 result, I often recommend the "least likely to be wrong" course of action instead
 of recommending investigation that requires expertise that the original poster
 might not possess ... after all, if they had the experience to do all the
 research, it's unlikely that they'd be asking this question in the first
 place.
2) The negative impact of an unused index is tiny compared to the negative
 impact of a missing index.
3) Dropping an unused index is _far_ less headache than creating a missing
 index on a busy database.
4) Without knowing _all_ the details of the app and how it works, my past
 experience is that problems are about a jillion times more likely to be
 the result of underindexing (although I _have_ seen issues due to
 overindexing, so it _does_ happen)

I can't argue that the _best_ course of action is to set up a simulation
that can exercise the system at predicted size and load levels and use that
to tune things. But communicating all that to others has never been easy
in my experience. As recently as this month my team was blown away that I
could create a simulation that demonstrated how my code would behave under
real-world like conditions. It's apparently not something that a lot of
people understand or are good at or something.

-- 
Bill Moran <wmoran@potentialtech.com>

On 23 February 2018 at 04:00, Bill Moran <wmoran@potentialtech.com> wrote:
> 2) The negative impact of an unused index is tiny compared to the negative
>  impact of a missing index.

I'd say that would entirely depend on the workload of the table and
the entire cluster. Disk space and I/O to write WAL and index pages to
is surely a finite resource. Not to mention the possibility of
disallowing HOT-Updates in the heap.

It would seem to me that anyone using the "shotgun" indexing method
may end up having to learn more about indexing the hard way.  Learning
the hard way is something I like to try to avoid, personally. Probably
it all has to come down to how important it is that your app actually
can handle the load vs devs/dba experience level divided by time, both
of the latter two are also a finite resource. So, it probably all has
to be carefully balanced and quite possibly a person's opinion
strongly relates to their experience.  If you were in the air traffic
control business, perhaps your opinion might not be the same!? ...
Sorry, the aeroplane crashed because the replica instance lagged and
the plane's location wasn't updated... Analysis shows that the DBA
indexed every column in the table and the WAL volume was more than the
network's bandwidth could handle over the holiday period.  (Note: I
know nothing about air traffic control, but it does seem like
something you'd want to make stable systems for, games on the
internet, probably less so...).


-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Fri, 23 Feb 2018 17:10:56 +1300
David Rowley <david.rowley@2ndquadrant.com> wrote:

> On 23 February 2018 at 04:00, Bill Moran <wmoran@potentialtech.com> wrote:
> > 2) The negative impact of an unused index is tiny compared to the negative
> >  impact of a missing index.
> 
> I'd say that would entirely depend on the workload of the table and
> the entire cluster. Disk space and I/O to write WAL and index pages to
> is surely a finite resource. Not to mention the possibility of
> disallowing HOT-Updates in the heap.

I feel like you missed my point. You're absolutely right, but the
disagreement was not on whether or not an unused index could cause
problems, but on the likelihood that the OP was going to build the
simulation code to actually determine whether the index is needed
or not. Information from the original question led me to believe
that simulation was either beyond his skill level or beyond his
time allocation; so I provided a less good, but more likely to be
helpful (in my opinion) answer.

The pattern that almost ALL new ventures I've seen follow is:
1) Hack something together based on an idea for a product
2) If the product actually succeeds, experience tons of issues
 related to scaling and performance
3) Run around like a crazy person fixing all the scaling and
 performance issues
4) Sell the company to someone else who ultimately becomes responsible
 for maturing the software

In theory, there's no reason this pattern _has_ to be so prevalent,
yet it is. Probably becuase it appears to minimize the up front cost,
which the people footing the bill just love.

Given that process, "shotgun" indexing is part of step 1 or step 3.
Whereas the building of load simulations and _real_ tuning of the
system is relegated to step 4.

Since investers tend to want to get out quick if #2 isn't going to
happen, they don't want people doing work that they consider part
of step #4.

I'm a pragmatist. I'd love to see everyone build software in a sane,
well-considered manner. I'd also love to see government without
corruption. However, in the world I _actually_ see, those things aren't
prevalent.

> It would seem to me that anyone using the "shotgun" indexing method
> may end up having to learn more about indexing the hard way.  Learning
> the hard way is something I like to try to avoid, personally. Probably
> it all has to come down to how important it is that your app actually
> can handle the load vs devs/dba experience level divided by time, both
> of the latter two are also a finite resource. So, it probably all has
> to be carefully balanced and quite possibly a person's opinion
> strongly relates to their experience.  If you were in the air traffic
> control business, perhaps your opinion might not be the same!? ...
> Sorry, the aeroplane crashed because the replica instance lagged and
> the plane's location wasn't updated... Analysis shows that the DBA
> indexed every column in the table and the WAL volume was more than the
> network's bandwidth could handle over the holiday period.  (Note: I
> know nothing about air traffic control, but it does seem like
> something you'd want to make stable systems for, games on the
> internet, probably less so...).

I really hope that people writing ATC software have the experience to
do really good testing (including load simulation, etc) but the 3 mile
island accident happened partially because of faulty sensor design, so
there's no guarantee that's the case.

Interesting discussion.

-- 
Bill Moran