{"id":56483,"date":"2024-07-22T17:46:59","date_gmt":"2024-07-22T17:46:59","guid":{"rendered":"https:\/\/dbtut.com\/?p=56483"},"modified":"2024-07-22T17:49:35","modified_gmt":"2024-07-22T17:49:35","slug":"b-tree-index-in-postgresql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2024\/07\/22\/b-tree-index-in-postgresql\/","title":{"rendered":"B-Tree Index In PostgreSQL"},"content":{"rendered":"
In today’s article, we will discuss the B-tree index, which is a balanced tree data structure used to store indexed values \u200b\u200bin a sorted order.<\/p>\n
It is one of the most used index types in database systems.<\/p>\n
It is one of the default index types in Oracle, MSSQL and PostgreSQL.<\/p>\n
Different index types can be selected with the USING command in the CREATE index command in PostgreSQL.<\/p>\n
If the USING command is not specified, it will use the B-tree index architecture.<\/p>\n
<\/p>\n
B-Tree indexes are kept in pieces as seen in the picture above.<\/p>\n
When you want to access data in B-tree indexes, you first look at the page at the root level and if the desired data is at this root level, you go to the relevant intermediate level of this root level and from there you go to the relevant leaf level and find the data in the shortest way.<\/p>\n
When you want to access the record numbered “22” from the B-tree structure above, you first go to the page at the Root level between “1-200”.<\/p>\n
From here, the data is quickly returned to the user by going to the intermediate level page between “1-100” in the levels intermediate and then going to the intermediate level between “1-50” and the next one, going to the data set between “1-25” and taking the record number 24. .<\/p>\n
<\/p>\n
You can think of a table without index as above.<\/p>\n
It will go to all points and try to search for the records there one by one, and therefore the return of our query will be long and costly.<\/p>\n
Non-indexed tables not only lengthen the query result, but also increase the CPU and disk usage rate and bottleneck the machine on which the database runs in terms of CPU and disk.<\/p>\n
B-tree indexes process equality and range queries on data that can be sorted in a certain order.<\/p>\n
In particular, the PostgreSQL query planner will prefer to use the B-tree index when a comparison is made using one of the following operators.<\/p>\n
<<\/p>\n
<=<\/p>\n
=<\/p>\n
>=<\/p>\n
><\/p>\n
BETWEEN<\/p>\n
IN<\/p>\n
IS NULL<\/p>\n
NOT NULL<\/p>\n
column\u00a0 LIKE \u2018faruk%\u2019<\/p>\n
column\u00a0 |\\\u2019faruk%\u2019<\/p>\n
SQL statements like above also use the B-Tree index structure.<\/p>\n
General Usage is as follows.<\/strong><\/p>\n
CREATE INDEX \u0130ndex_adi ON Tablo_Adi USING btree (Column_\u0130smi );\r\n<\/pre>\n
Example usage is as follows.\u00a0<\/strong><\/p>\n
CREATE INDEX ad ON \"M\u00fcsteriler\"(\"Adi_Soyadi\" )\r\n<\/pre>\n
We want to fetch data containing the word “Texas” in the district column in our Address table.<\/p>\n
When we make a normal query, we see that it performs seq scan as follows.<\/p>\n
<\/p>\n
Since there is a district column in the where condition in our plpgsql query, we create an index for the district column as follows.<\/p>\n
create index IX_address_district on address (district)<\/pre>\n
After the index is created, we see that it performs Index Only Scan as follows.<\/p>\n
<\/p>\n
When defining the index, definitions can be made in the form of asc, desc, nulls, null first and null last.<\/p>\n
Asc:<\/strong> Ascending is defined to create the index according to the order.<\/p>\n
Desc:<\/strong> Descending is defined to create the index according to the order.<\/p>\n
NULL FIRST:<\/strong> It is used to place null records in the first rows in the index.<\/p>\n
NULL LAST:<\/strong> It is used to place null records in the last rows of the index.<\/p>\n
Different operator classes can be defined when defining the index.<\/p>\n
If you are using varchar data type, you can create varchar_pattern_ops.<\/p>\n
Unless dealing with complex data, varchar will be sufficient.<\/p>\n
In order to use operator classes, the collate of the database must be set to C.<\/p>\n
If you want to see all operator classes, you can use the plgpsql command below.<\/p>\n
SELECT am.amname AS index_method,\r\nopc.opcname AS opclass_name\r\nFROM pg_am am, pg_opclass opc\r\nWHERE opc.opcmethod = am.oid\r\nand am.amname='btree'\r\nORDER BY index_method, opclass_name;<\/pre>\n
![](\"https:\/\/www.farukerdem.com\/wp-content\/uploads\/2021\/03\/btreeindex.png\")
<\/p>\n![](\"https:\/\/www.farukerdem.com\/wp-content\/uploads\/2021\/03\/btreepoint.png\")
<\/p>\n![](\"https:\/\/www.farukerdem.com\/wp-content\/uploads\/2021\/03\/seqscan.png\")
<\/p>\n![](\"https:\/\/www.farukerdem.com\/wp-content\/uploads\/2021\/03\/indexonlyscan.png\")
<\/p>\n