Indexing

-- Create a BTree index (default when you omit USING)
CREATE INDEX idx_users_email ON users (email);

-- This query will now use the index
SELECT * FROM users WHERE email = 'alice@example.com';

-- Explicit USING BTREE (same as default)
CREATE INDEX idx_orders_user ON orders USING BTREE (user_id);

-- Range queries — BTree handles all of these
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

SELECT * FROM products WHERE price BETWEEN 100 AND 500;

SELECT * FROM users WHERE name >= 'A' AND name < 'B';   -- names starting with A

-- Operators supported by BTree:
--   =   <   <=   >   >=   BETWEEN   IN   IS NULL   LIKE 'abc%' (prefix only)

CREATE TABLE orders (
  order_id   BIGSERIAL PRIMARY KEY,
  user_id    BIGINT       NOT NULL,
  status     TEXT         NOT NULL,  -- 'pending','paid','shipped','cancelled'
  total      NUMERIC(10,2),
  created_at TIMESTAMPTZ  DEFAULT NOW()
);

-- Index on user_id to find orders for a specific user
CREATE INDEX idx_orders_user_id  ON orders (user_id);

-- Index on created_at for date-range dashboard queries
CREATE INDEX idx_orders_created  ON orders (created_at);

-- These now use the indexes:
SELECT * FROM orders WHERE user_id = 1001;
SELECT * FROM orders WHERE created_at >= '2025-01-01';

-- Without index: scan all rows, sort in memory (expensive)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

-- With index on created_at: index scan in reverse order, no sort needed ✓
CREATE INDEX idx_orders_created ON orders (created_at DESC);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

-- Hash index: use USING HASH explicitly
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- This equality query will use the hash index
SELECT user_id, expires_at
FROM   sessions
WHERE  session_token = 'eyJhbGci...';

-- Practical note: In practice, BTree is almost always preferred
-- because it also handles ranges and sorts. Hash wins only when
-- equality is guaranteed to be the ONLY access pattern.

-- Example: Products with a JSONB metadata column
CREATE TABLE products (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT,
  metadata JSONB   -- {"brand":"Nike","size":"L","color":"red","tags":["sports","sale"]}
);

-- GIN index on the whole JSONB column
CREATE INDEX idx_products_meta ON products USING GIN (metadata);

-- Now these queries use the GIN index:

-- Does JSON contain a key-value?
SELECT * FROM products WHERE metadata @> '{"brand":"Nike"}';

-- Does JSON contain this top-level key?
SELECT * FROM products WHERE metadata ? 'brand';

-- Does array field contain this element?
SELECT * FROM products WHERE metadata -> 'tags' @> '"sale"';

CREATE TABLE posts (
  id    BIGSERIAL PRIMARY KEY,
  title TEXT,
  tags  TEXT[]   -- array of tag strings
);

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Contains operator @> — does the tags array contain 'postgres'?
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

-- Overlap operator && — does tags array share any element with this array?
SELECT * FROM posts WHERE tags && ARRAY['postgres', 'sql'];

CREATE TABLE articles (
  id      BIGSERIAL PRIMARY KEY,
  title   TEXT,
  body    TEXT,
  tsv     TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED
);

-- GIN on the pre-computed tsvector column (fastest)
CREATE INDEX idx_articles_tsv ON articles USING GIN (tsv);

-- Full text search now uses the GIN index
SELECT title, ts_rank(tsv, query) AS rank
FROM   articles, to_tsquery('english', 'postgresql & index') query
WHERE  tsv @@ query
ORDER BY rank DESC;

-- PostGIS extension for geospatial data
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
  id      BIGSERIAL PRIMARY KEY,
  name    TEXT,
  coords  GEOMETRY(POINT, 4326)   -- latitude/longitude
);

-- GiST index on the geometry column
CREATE INDEX idx_locations_coords ON locations USING GIST (coords);

-- Find all restaurants within 5km of a point
SELECT name,
       ST_Distance(coords, ST_MakePoint(77.5946, 12.9716)::geography) AS dist_m
FROM   locations
WHERE  ST_DWithin(coords::geography,
                  ST_MakePoint(77.5946, 12.9716)::geography,
                  5000)   -- 5000 metres
ORDER BY dist_m;

-- The GiST index makes this fast using bounding-box pruning

CREATE TABLE hotel_bookings (
  id       BIGSERIAL PRIMARY KEY,
  room_id  INT,
  booked   DATERANGE   -- e.g. [2025-01-10, 2025-01-15)
);

CREATE INDEX idx_bookings_dates ON hotel_bookings USING GIST (booked);

-- Overlapping range query — find all bookings that overlap [Jan 12, Jan 14)
SELECT * FROM hotel_bookings
WHERE  booked && '[2025-01-12, 2025-01-14)'::daterange;

-- This powers the EXCLUDE constraint for no double-booking:
ALTER TABLE hotel_bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING GIST (room_id WITH =, booked WITH &&);

-- pg_trgm: trigram similarity for fuzzy text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE products (id SERIAL, name TEXT);

-- GiST trigram index allows LIKE '%abc%' and similarity() queries
CREATE INDEX idx_products_name_trgm ON products USING GIST (name gist_trgm_ops);

-- Fuzzy search: find names similar to 'postgress' (typo)
SELECT name, similarity(name, 'postgress') AS sim
FROM   products
WHERE  name % 'postgress'   -- % = similarity threshold (default 0.3)
ORDER BY sim DESC;

-- LIKE with wildcard (both sides) — works with trgm index
SELECT * FROM products WHERE name LIKE '%ostgre%';

-- IoT sensor data: billions of rows, append-only
CREATE TABLE sensor_readings (
  id         BIGSERIAL PRIMARY KEY,
  sensor_id  INT,
  value      FLOAT,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- BRIN index: tiny size, fast to build, perfect for time-series range queries
CREATE INDEX idx_sensor_brin ON sensor_readings USING BRIN (recorded_at);

-- Pages_per_range controls granularity (smaller = more precise, bigger index)
CREATE INDEX idx_sensor_brin_fine ON sensor_readings
  USING BRIN (recorded_at) WITH (pages_per_range = 32);

-- This query uses the BRIN index to skip irrelevant page ranges
SELECT AVG(value) FROM sensor_readings
WHERE  recorded_at >= '2025-01-01' AND recorded_at < '2025-02-01'
AND    sensor_id = 42;

-- Composite index on (user_id, created_at)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- ✓ Uses index: prefix match (user_id is the leading column)
SELECT * FROM orders WHERE user_id = 42;

-- ✓ Uses index: both columns
SELECT * FROM orders WHERE user_id = 42 AND created_at >= '2025-01-01';

-- ✗ CANNOT use index: created_at is not the leading column
SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- (for this you'd need a separate index on created_at)

-- Example: orders table queried like:
--   WHERE user_id = ?                        (frequent)
--   WHERE user_id = ? AND status = ?         (frequent)
--   WHERE user_id = ? AND created_at > ?     (frequent)
--   WHERE status = ?                         (rare, dashboard)

-- Good: user_id first (most selective in user context)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
CREATE INDEX idx_orders_user_date   ON orders (user_id, created_at);

-- Rare status-only query: either add separate index or accept seq scan
CREATE INDEX idx_orders_status ON orders (status) WHERE status != 'cancelled';
-- ^ partial index (explained in 9.8)

─────────────────────────────────────────────────────────────
 Use case 1: Active records only
 Orders are mostly 'cancelled' or 'delivered'. You only query
 'pending' or 'processing' orders on your dashboard.
─────────────────────────────────────────────────────────────
CREATE INDEX idx_orders_active
  ON orders (created_at)
  WHERE status IN ('pending', 'processing');

-- This query uses the partial index (condition matches)
SELECT * FROM orders
WHERE  status = 'pending'
AND    created_at < NOW() - '1 hour'::interval;

─────────────────────────────────────────────────────────────
 Use case 2: Soft deletes — never query deleted rows
─────────────────────────────────────────────────────────────
CREATE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

-- Only active users are in the index
SELECT * FROM users WHERE email = 'alice@x.com' AND deleted_at IS NULL;

─────────────────────────────────────────────────────────────
 Use case 3: Unique constraint on non-null values only
─────────────────────────────────────────────────────────────
CREATE UNIQUE INDEX idx_users_phone_unique
  ON users (phone)
  WHERE phone IS NOT NULL;
-- Multiple users can have NULL phone, but non-null phones must be unique

-- Without INCLUDE: index has user_id only
CREATE INDEX idx_users_id ON users (user_id);
-- Query must still fetch heap to get email and name

-- With INCLUDE: add the columns you SELECT into the index
CREATE INDEX idx_users_id_cover
  ON users (user_id)
  INCLUDE (email, name);

-- Now this query is an Index-Only Scan (heap never touched!)
SELECT email, name FROM users WHERE user_id = 42;

-- EXPLAIN will show: Index Only Scan (not "Index Scan")
EXPLAIN SELECT email, name FROM users WHERE user_id = 42;

-- orders table with many columns
-- API endpoint: GET /orders?user_id=X → returns [order_id, status, total, created_at]

-- Covering index for this exact API query
CREATE INDEX idx_orders_api_cover
  ON orders (user_id, created_at DESC)
  INCLUDE (order_id, status, total);

-- This query now does an Index-Only Scan — zero heap fetches
SELECT order_id, status, total, created_at
FROM   orders
WHERE  user_id = 1001
ORDER BY created_at DESC
LIMIT  20;