- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What kind of indexing can be done on a PostgreSQL table?
Indexing is used to speed up query execution in PostgreSQL, and any relational database in general. PostgreSQL tables primarily support several index types. Let us discuss 3 frequently user index types in brief −
These indexes can only handle equality comparisons. In other words, if I want to check if itemA = itemB, then hash indexes are useful. It is not suited for other types of operations, like >, <. >=, <=. Suppose I have a marks table, defined below −
Now, if I can add the hash index on the roll_no column as follows −
CREATE INDEX roll_no_index ON marks USING hash (roll_no);
For queries involving equal to comparisons on the roll_no column, this index will be used. An example is given below −
SELECT * from marks WHERE roll_no = 12
However, if you have other types of comparisons, like
SELECT * from marks where roll_no > 40
then this index will be useless, and PostgreSQL will organize the query plan assuming the index doesn’t exist.
This is the default index used by PostgreSQL. In other words, if you don’t explicitly specify an index type, PostgreSQL will create a Btree index. This index is used by PostgreSQL for all the comparison operations (>=, <=, >, <, =). This makes it better than Hash index. It can be created using the following command −
CREATE INDEX index_name on table_name (columnName1, columnName2,…)
As you can see from the above syntax, you can also create the multi-column index. Whether to create single-column index or multi-column index depends on whether one or multiple columns will be frequently used in the WHERE part of the queries made to the table.
GiST stands for Generalized Search Tree. This index is used very frequently with a popular extension of PostgreSQL, called PostGIS. PostGIS is used for performing geo-spatial queries on geometry objects. Now operations like contains, intersects, etc. cannot benefit from B-tree index (which can only handle comparisons and equality checks). GiST index comes in very handy here. It is generally applied on the geometry column of the table like −
CREATE INDEX geom_index on myGeomTable USING gist(geom)
A very good explanation of GIS is given here − https://postgis.net/workshops/postgisintro/indexing.html
Apart from these index types, there are other index types as well: GIN, BRIN, etc. You can read more about them here − https://www.enterprisedb.com/postgres-tutorials/overview-postgresql-indexes
- What all constraints can be added to a PostgreSQL table?
- Create Primary Key on an existing table in PostgreSQL?
- What can be done to control water pollution?
- What kind of SQL statements can be used to prepare statements?
- What kind of compound units can be used in MySQL EXTRACT() function?
- What Kind Of Soil Can Be Used For Making Matkas And Surahis ?
- How to create a table in PostgreSQL?
- From a business perspective, what can be done with used plastic bottles?
- What kind of string comparison, case-sensitive or not, can be performed by MySQL?
- What could be some easy exercises that could be done sitting on a chair in office?
- How can CONCAT() function be applied on columns of MySQL table?
- How can nitrogen fixation be done by lightning?
- What kind of mirror can have a focal length of, −20 cm?
- At what kind of situations LCM has to be used ?