If you are at all confused about which of PostgreSQL index to choose for a specific use case, there's a  free course from Percona University designed to help you.

This free Percona University short course about the different kinds of indexes PostgreSQL supports. is available as a collection of videos on YouTube as well as on Google Classroom. It goes over the different types of indexes, when to use each and how to usethem  optimally in order to squeeze performance out. The premise is that not all indexes are appropriate for all circumstances and using a ‘wrong’ index can have the opposite effect to that you intended.

PostgreSQL has the following types of indexes;the popular Hash and B-tree, and the more exotic ,GiST, SP-GiST, GIN and BRIN.

I'll give a rough overview of each type:

• Hash Consists of the hashed value of the key it is applied on. Used for equality operations.
  
  
• BTree Lets you search by inequality,equality and range. A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.  
  
  
• GIST Like a BTree, but for more complex cases like spatial data.
  
  
• SP-GiST  For Space Partitioning trees; splitting data into dimensions
  
  
• GIN - Generalized Inverted Index for full-text search
  
  
• BRIN For large datasets

After a brief overview of what an index actually is, we are shown how to create Partial (index only part of table rows) and Expression  (index on expression,not field) Indexes. The course then moves on to tackle the various kinds.

The real value lies with lessons 10,11,12 and 13,where you get to learn in which scenarios each type is appropriate, how to avoid table scans for index only scans and how to keep your house clean by finding duplicate and unused indexes.

In detail the syllabus is :

Lesson 1: Overview
Lesson 2: Heap vs Index
Lesson 3: How to Create Indexes?
Lesson 4: How to create an Expression Index?
Lesson 5: Advantages of a Partial Index
Lesson 6: Index Types: B-Tree
Lesson 7: Index Types: What is HASH Index?
Lesson 8: Index Types: What is a BRIN Index?
Lesson 9: Index Types: GIN and GIST
Lesson 10: How to use different types of Indexes?
Lesson 11: Index Only Scans
Lesson 12: How to Find Duplicate Indexes?
Lesson 13: Unused Indexes

Upon completion you can file for a Certificate of Completion after you pass a quiz consisting of 23 multiple choice questions, which requires you to get a minimum of 17 answers correct.

The videos themselves are 2 to 5 mins long; so grab a coffee and in about 45 minutes you'll have a good overview of the concept of indexes and their use while, of course, not becoming an expert. The videos are scattered around Percona's YouTube channel and while they can be found in the comments of each video for the next one,  it's best to attend the Classroom which links to them in lesson order.

In the end, even if you are not interested in the exotic type of indexes, you'll still get encyclopedic value out of this course due to its quick and uncomplicated look on the basics.

More Information

Google Classroom

Lesson 1 on YouTube

Related Articles

The Enduring Influence Of Postgres

PostgreSQL Is DB-Engines DBMS of the Year For 2020

SQL Snippets - Crowdsourcing SQL Queries 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info