I’ve always heard that databases with proper indexing can perform much faster than non-indexed versions. So I decided to set up a little test to check this out.

I created an indexed and non-indexed version of my Scrabble Helper Postgres DB

docker pull lombardo/postgres-scrabble-helper:0.1 => non-indexed

docker pull lombardo/postgres-scrabble-helper:1.0 => indexed

The syntax for adding an index to a Postgres table is easy:

-- CREATE INDEX <index_name> ON <table_name> (<column_name>)
CREATE INDEX canonical_word_index ON words (canonical_word);

Postgres uses the B-tree index method by default, and that is fine for our purposes.

I wired up both versions of the SQL database to my Scrabble Helper API and here’s a comparison of performance.

The ** in the query indicates 2 wildcards (aka the blank Scrabble tiles)

Query Number of queries Non-Indexed DB Indexed DB
GET /words/testing 72 1307 ms 115 ms
GET /words/testing** 23,986 415855 ms (~7 minutes) 10548 ms (10.5 seconds)
GET /words/indexxedni 216 3680 ms 166 ms
GET /words/indexxedni** 65,925 1155465 ms (~19 minutes) 26597 ms (26.6 seconds)

–

Needless to say these results speak for themselves…