Indexes

An index:

• Is a schema object
• Can be used by the PostgreSQL server to speed up the retrieval of rows by using a pointer
• Can reduce disk I/O by using a rapid path access method to locate data quickly
• Is independent of the table that it indexes
• Is used and maintained automatically by the PostgreSQL server

How Are Indexes Created?

• Automatically : A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

• Manually : Users can create non unique indexes on columns to speed up access to the rows.

Creating an Index

• Create an index on one or more columns : 

         CREATE INDEX index ON table (column[, column]...);

• Improve the speed of query access to the 
• LAST_NAME column in the EMPLOYEES table :

         CREATE INDEX emp_last_name_idx ON employees(last_name);

Index Creation Guidelines :

Create an index when :

• A column contains a wide range of values
• A column contains a large number of null values
• One or more columns are frequently used together in a WHERE clause or a join condition
• The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

Do not create an index when :

• The columns are not often used as a condition in the query
• The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
• The table is updated frequently
• The indexed columns are referenced as part of an expression

Removing an Index

• Remove an index from the data dictionary by using the DROP INDEX command :

          DROP INDEX index;

• Remove the UPPER_LAST_NAME_IDX index 
• from the data dictionary :

          DROP INDEX emp_last_name_idx;

Sequences

A sequence:

• Can automatically generate unique numbers
• Is a sharable object
• Can be used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memory

CREATE SEQUENCE Statement: 

• Define a sequence to generate sequential numbers automatically
• Syntax:

        CREATE [ TEMPORARY | TEMP ] SEQUENCE name         
        [ INCREMENT [ BY ] increment ]         
        [ MINVALUE minvalue | NO MINVALUE ]         
        [ MAXVALUE maxvalue | NO MAXVALUE ]        
        [ START [ WITH ] start ]        
        [ CACHE cache ] [ [ NO ] CYCLE ]         
        [ OWNED BY { table.column | NONE } ]

Creating a Sequence

• Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. 
• Do not use the CYCLE option.

        CREATE SEQUENCE dept_deptid_seq 
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NO CYCLE;

NEXTVAL and CURRVAL Pseudocolumns

• NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.

Using a Sequence

• Insert a new department named “Support” in location Boston:

        INSERT INTO departments 
                       (department_id, department_name, location_id)
        VALUES (NEXTVAL(‘dept_deptid_seq’), 'Support', ‘Boston’);

• View the current value for the DEPT_DEPTID_SEQ sequence:

        SELECT CURRVAL(‘dept_deptid_seq’);

Caching Sequence Values

• Caching sequence values in memory gives faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table

Modifying a Sequence

• Change the increment value, maximum value, minimum value, cycle option, or cache option:

        ALTER SEQUENCE dept_deptid_seq
                    INCREMENT BY 20
                    MAXVALUE 999999
                   NO CYCLE;