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.
|
1 |
create table indextabled1(column1 integer, column2 text, column3 boolean); |
After creating our table, let’s load our random data as follows.
|
1 2 3 4 |
insert into indextabled1(column1,column2,column3) select tt.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as tt(id) order by random(); |
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.
|
1 2 |
explain (costs off ) select*from indextabled1 where kolon1= 91547 |
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.
|
1 |
create index IX_1 on indextabled1(column1) |
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):
|
1 2 |
explain (costs off ) select*from indextabled1 where kolon1< 21 and kolon2='F' |
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.
|
1 |
create index IX_2 on indextabled1(kolon1, kolon2) |
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.
|
1 |
Drop index IX_2; |
After deleting our index, let’s create two separate indexes as follows.
|
1 2 |
create index IX_3 on indextabled1(kolon2) create index IX_4 on indextabled1(kolon1) |
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.
