Database Reference
In-Depth Information
An index can be created utilizing a single column or multiple columns of a table.
Once the index is created, then there is no further intervention needed; the index
will be automatically updated on each DML operation on the table. After creating
the index, it is the planner's decision to utilize the index in lieu of sequential scans
based on cost.
When PostgreSQL executes a query, it must choose an execution
strategy. The planner is the piece of software that is in charge of
establishing the best strategy to perform an eficient search.
Suppose we have a table named
item
that contains columns of
item_id
,
item_name
,
item_price
, and
item_data
and contains millions of rows. Let's connect to the
warehouse_db
database via the
psql
command-line utility and create the
item
table
in the following manner:
warehouse_db=# CREATE TABLE item
(
item_id INTEGER NOT NULL,
item_name TEXT,
item_price NUMERIC,
item_data TEXT
);
The result can be seen using the following statement:
warehouse_db=# SELECT item_name FROM item WHERE item_id = 100;
If we want to get the details of a speciic item using the preceding query, then the
whole table should be scanned to ind the item whose
item_id
is
100
, which is a
serious performance issue. This problem can be solved using indexes.
The
EXPLAIN QUERY
command can be used to check which scan will be
used for the query. This can be explained with the following example:
warehouse_db=# EXPLAIN SELECT * FROM table_name;
QUERY PLAN
-----------------------------------------------------
Seq Scan on table_name
(cost=10000000000.00..10000000001.01 rows=1 width=76)
Planning time: 0.071 ms
