🐘 PostgreSQL -
Indexes

Updated at 2015-11-18 22:39

Indexes make searches faster in file storage's expense.

-- Simple index
CREATE INDEX idx_salary ON employees (salary);

-- Multicolumn index
CREATE INDEX idx_last_name_salary ON employees (last_name, salary);

-- Creating an index locks the table so you will want to do it concurrently
-- on bigger tables that avoids full locking.
CREATE INDEX CONCURRENTLY idx_last_name_salary ON employees(last_name, salary);

PostgreSQL automatically creates an index for each unique constraint and the primary key. No need to create your own.

--  DROP TABLE users;
CREATE TABLE users (
  user_id     uuid        NOT NULL DEFAULT uuid_generate_v4(),
  name        text        NOT NULL,
  created_at  timestamp   DEFAULT clock_timestamp(),
  updated_at  timestamp   DEFAULT clock_timestamp(),
  CONSTRAINT  users_pkey  PRIMARY KEY (user_id)
);
-- user_id is automatically indexed

If table has 10,000+ rows and cache ratio is less than 99%, try adding an index.

SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) AS percent_of_times_index_used,
    n_live_tup AS rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

EXPLAIN ANALYZE SELECT * FROM events WHERE app_info_id = 7559;

CREATE INDEX CONCURRENTLY idx_events_app_info_id ON events (app_info_id);

EXPLAIN ANALYZE SELECT * FROM events WHERE app_info_id = 7559;

Index Types

You can specify which indexing method to use with the optional USING statement.

CREATE INDEX index_users_last_name ON users USING btree (last_name)

btree: Default indexing method. This default is usually good choice.
gist: Tree indexing with advanced query capabilities Should be used with text search and positional data.
gin: Indexing of composite data types. Should be used with JSON and JSONB data.

Expression Indexes

-- create index of lower-cased URLs
CREATE INDEX idx_webhits_lower_urls ON webhits (lower(url));

Partial Indexes

If you want to apply indexing only to a subset of a table.

-- create index of only paying customers
CREATE INDEX idx_paying_accounts ON accounts (account_id)
WHERE account_type <> 'test';

Index Uniqueness

CREATE UNIQUE INDEX user_email ON users (email) WHERE deleted_at IS NULL;

Sources