Sunday , November 10 2024

PostgreSQL Multicolumn Indexes

In this tutorial, you will learn how to create PostgreSQL multicolumn indexes, which are indexes defined on two or more columns of a table.

First, let’s create our table as follows.

After creating our table, let’s load our random data as follows.

We loaded our data and let’s see how it scans while bringing us the data of 91547 records in column 1 of the indextabled1 table.

Since no index has been created in our table and there is no index in column1 that we used in the where condition, seqscan is performed.

Let’s create an index on the column named column1 that we used in the where condition and look at the used index again.

After creating our index, let’s run our query again with explain and see how the index scans.

As seen above, after creating the index, our query performs index scan instead of seqscan.

In the example we used, we used a single where condition, but what do we need to do when there is more than one condition?

Can we define two columns in a single index? Will we create two separate indexes?

Both can be done, but let’s see together which one will be more efficient.

First, let’s define two columns in a single index. (Multi Column Index):

As a result of the query above, it is seen that our query performs a bitmap index scan.

Let’s create a single index that includes columns column1 and column2.

As seen above, when we created a Multicolumn index, it used the index we created by making an index while fetching records.

What would be the result if we created two different indexes?

First, let’s delete the index in our previous example.

After deleting our index, let’s create two separate indexes as follows.

When we create two indexes, we see that the query plan is longer and that it unnecessarily performs a Bitmap heap scan and a Bitmap index scan of the indexes we create.

We see that creating two separate indexes causes more performance loss in this query result.

In case of multiple where conditions, instead of making them separate indexes, making a single index both takes up more space on the disk and works with better performance.

Loading

About Faruk Erdem

Leave a Reply

Your email address will not be published. Required fields are marked *