CREATE INDEX

-- Create index with specific columns
CREATE INDEX [IF NOT EXISTS] <index_name> 
  ON <object_name> (<column_expr> [, ...])
  [IN CLUSTER <cluster_name>];

-- Create default index
CREATE DEFAULT INDEX [IF NOT EXISTS] <index_name>
  ON <object_name>
  [IN CLUSTER <cluster_name>];

CREATE VIEW user_orders AS
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id;

-- Create index to maintain results in memory
CREATE INDEX user_orders_idx ON user_orders (user_id);

-- Queries can now read directly from memory
SELECT * FROM user_orders WHERE user_id = 123;  -- Very fast!

-- Create index in cluster_a
SET cluster = cluster_a;
CREATE INDEX idx ON my_view (column);

-- Query from cluster_a uses the index (fast)
SELECT * FROM my_view WHERE column = 'value';

-- Query from cluster_b does NOT use the index
SET cluster = cluster_b;
SELECT * FROM my_view WHERE column = 'value';  -- Slower

CREATE DEFAULT INDEX user_stats_idx ON user_stats;

((indexed columns), (all columns in the view))

CREATE VIEW v AS 
  SELECT user_id, name, email FROM users;

CREATE INDEX v_idx ON v (user_id);
-- Stores: ((user_id), (user_id, name, email))

CREATE VIEW active_users AS
  SELECT * FROM users
  WHERE last_login > NOW() - INTERVAL '30 days';

CREATE INDEX active_users_idx ON active_users (user_id);

CREATE VIEW orders_by_customer AS
  SELECT customer_id, order_date, amount
  FROM orders;

CREATE INDEX orders_customer_date_idx 
  ON orders_by_customer (customer_id, order_date);

-- Optimizes queries like:
SELECT * FROM orders_by_customer 
WHERE customer_id = 123 AND order_date >= '2024-01-01';

CREATE VIEW users_upper AS
  SELECT user_id, name, email FROM users;

-- Index on uppercase email
CREATE INDEX users_upper_email_idx 
  ON users_upper (upper(email));

-- Optimizes queries like:
SELECT * FROM users_upper 
WHERE upper(email) = '[email protected]';

CREATE VIEW customers AS
  SELECT customer_id, name FROM customer_source;

CREATE VIEW orders AS
  SELECT order_id, customer_id, amount FROM order_source;

-- Index on join column
CREATE INDEX customers_idx ON customers (customer_id);
CREATE INDEX orders_customer_idx ON orders (customer_id);

-- This join is now efficient
CREATE VIEW customer_orders AS
  SELECT c.name, o.order_id, o.amount
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id;

CREATE VIEW product_sales AS
  SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_revenue
  FROM order_items
  GROUP BY product_id;

CREATE INDEX product_sales_idx ON product_sales (product_id);

-- Fast lookups by product
SELECT * FROM product_sales WHERE product_id = 456;

CREATE MATERIALIZED VIEW user_stats AS
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY user_id;

-- Speed up queries on the materialized view
CREATE INDEX user_stats_idx ON user_stats (user_id);

CREATE INDEX users_id_idx ON users (id);

-- Optimized by index
SELECT * FROM users WHERE id = 123;

CREATE INDEX orders_date_idx ON orders (order_date);

-- Optimized by index
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-02-01';

CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX customers_id_idx ON customers (id);

-- Join uses indexes for efficient lookup
SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id;

CREATE INDEX orders_multi_idx 
  ON orders (customer_id, status);

-- Optimized by compound index
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';

-- View has 1M rows
-- Each row: user_id (8 bytes) + name (20 bytes) + email (30 bytes)
-- Index key: user_id (8 bytes)
-- Index value: user_id + name + email (58 bytes)
-- Total per row: ~66 bytes
-- Total memory: ~66 MB (plus overhead)