PostgreSQL: PostgreSQL インデックスの内部構造と最適化

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

 indexname              | indexdef
------------------------+--------------------------------------------------
 orders_pkey            | CREATE UNIQUE INDEX ... ON orders USING btree (order_id)
 idx_orders_customer_id | CREATE INDEX ... ON orders USING btree (customer_id)

CREATE INDEX idx_orders_amount ON orders (amount);

CREATE INDEX idx_orders_date_amount ON orders (order_date, amount DESC);

CREATE TABLE

CREATE UNIQUE INDEX idx_users_email ON users (email);

CREATE TABLE

CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

SELECT product_id, name
FROM products
WHERE attrs @> '{"category": "electronics"}';

 product_id |    name
------------+------------
        101 | Laptop Pro
        205 | Smart Watch

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT product_id, name
FROM products
WHERE tags @> ARRAY['summer', 'sale'];

  result  
----------
   42.50
(1 row)

CREATE INDEX idx_articles_body ON articles USING GIN (to_tsvector('english', body));

SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgresql & index');

CREATE TABLE

CREATE INDEX idx_events_time_range ON events USING GiST (time_range);

SELECT event_id, title
FROM events
WHERE time_range && daterange('2025-01-01', '2025-03-01');

CREATE TABLE

CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

SELECT count(*) FROM logs
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

 count 
-------
     5
(1 row)

-- 最初にbtree_gist拡張機能を有効化: CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_customers_phone ON customers USING SP-GiST (phone prefix_range);

CREATE TABLE

CREATE INDEX idx_users_active_email ON users (email)
WHERE status = 'active';

CREATE TABLE

SELECT email FROM users WHERE status = 'active' AND email = 'alice@example.com';

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE shipped = false;

CREATE TABLE

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

CREATE TABLE

CREATE INDEX idx_orders_date_trunc ON orders (DATE_TRUNC('day', created_at));

SELECT COUNT(*) FROM orders
WHERE DATE_TRUNC('day', created_at) = '2025-06-15'::date;

 count 
-------
     5
(1 row)

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

CREATE TABLE

CREATE INDEX CONCURRENTLY idx_orders_region
ON orders (region);

CREATE TABLE

EXPLAIN
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=72)
   Index Cond: (customer_id = 1)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   (cost=0.29..8.31 rows=1 width=72) (actual time=0.015..0.016 rows=2 loops=1)
   Index Cond: (customer_id = 1)
 Planning Time: 0.085 ms
 Execution Time: 0.032 ms

EXPLAIN
SELECT customer_id FROM orders WHERE customer_id = 1;

 Index Only Scan using idx_orders_customer_id on orders
   Index Cond: (customer_id = 1)

REINDEX INDEX idx_orders_customer_id;

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- SQL statement executed successfully

SELECT * FROM users WHERE phone = 13800138000;

SELECT * FROM users WHERE phone = '13800138000';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE INDEX CONCURRENTLY idx_products_attrs_gin
  ON products USING GIN (attrs);

CREATE INDEX CONCURRENTLY idx_users_active_email
  ON users (email, last_login_at)
  WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, order_date DESC);

CREATE INDEX CONCURRENTLY idx_audit_log_created_brin
  ON audit_log USING BRIN (created_at)
  WITH (pages_per_range = 32);

EXPLAIN ANALYZE
SELECT p.product_id, p.name, p.attrs
FROM products p
WHERE p.attrs @> '{"category": "electronics", "in_stock": true}';

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE status = 'active'
  AND email LIKE 'alice%'
ORDER BY last_login_at DESC
LIMIT 10;