PostgreSQL - INDEXES

 Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers.

An index helps to speed up SELECT queries and WHERE clauses; however, it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

The CREATE INDEX Command

The basic syntax of CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;


Index Types

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

Single-Column Indexes

A single-column index is one that is created based on only one table column. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column_name);


Multicolumn Indexes

A multicolumn index is defined on more than one column of a table. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Whether to create a single-column index or a multicolumn index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the multicolumn index would be the best choice.

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows −

CREATE UNIQUE INDEX index_name
on table_name (column_name);


Partial Indexes

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. The basic syntax is as follows −

CREATE INDEX index_name
on table_name (conditional_expression);


Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Example

The following is an example where we will create an index on COMPANY table for salary column −

# CREATE INDEX salary_index ON COMPANY (salary);

Now, let us list down all the indices available on COMPANY table using \d company command.

# \d company

This will produce the following result, where company_pkey is an implicit index, which got created when the table was created.

       Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

You can list down the entire indexes database wide using the \di command −

The DROP INDEX Command

An index can be dropped using PostgreSQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

The basic syntax is as follows −

DROP INDEX index_name;

You can use following statement to delete previously created index −

# DROP INDEX salary_index;


When Should Indexes be Avoided?

Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered −

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch update or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.

Comments

Post a Comment

Popular posts from this blog

LINUX Move and copy files using SSH

Often you will need to move one or more files/folders or copy them to a different location. You can do so easily using an SSH connection. The commands which you would need to use are mv (short from move) and cp (short from copy). The mv command syntax looks like this: mv configuration.php-dist configuration.php By issuing the above command we will move (rename) the file configuration.php-dist to configuration.php. You can also use mv to move a whole directory and its content: mv includes/* ./ This will move all files (and folders) in the includes/ directory to the current working directory. In some cases however, we will need to only update the files and move only files that were changed, which we can do by passing ‘ -u ’ as argument to the command: mv -u includes/* admin/includes The copy (cp) command works the same way as mv, but instead of moving the files/folders it copies them. For example: cp configuration.php-dist configuration.php The command will copy the co
Read more

PHP Predefined Variables

PHP provides a large number of predefined variables to any script which it runs.PHP provides an additional set of predefined arrays containing variables from the web server the environment, and user input. These new arrays are called superglobals: All the following variables are automatically available in every scope. PHP Superglobals: Variable Description $GLOBALS Contains a reference to every variable which is currently available within the global scope of the script. The keys of this array are the names of the global variables. $_SERVER This is an array containing information such as headers, paths, and script locations. The entries in this array are created by the web server. There is no guarantee that every web server will provide any of these. See next section for a complete list of all the SERVER variables. $_GET An associative array of variables passed to the current script via the HTTP GET method. $_POST An associative array of variable
Read more

Java : Variables Declaring

[1]. Java Variables Variables are containers for storing data values. In Java, there are different types of variables, for example: String - stores text, such as "Hello". String values are surrounded by double quotes int - stores integers (whole numbers), without decimals, such as 123 or -123 float - stores floating point numbers, with decimals, such as 19.99 or -19.99 char - stores single characters, such as 'a' or 'B'. Char values are surrounded by single quotes boolean - stores values with two states: true or false [2]. Declaring (Creating) Variables To create a variable, you must specify the type and assign it a value: Syntax type variable = value ; Where type is one of Java's types (such as int or String), and variable is the name of the variable (such as x or name). The equal sign is used to assign values to the variable. To create a variable that should store text, look at the following example: Example Create a variable called  name  of type  Stri
Read more