CREATE INDEX trong PostgreSQL

CREATE TABLE orders (
  id int PRIMARY KEY,
  customer_id int NOT NULL,
  customer text NOT NULL,
  status text NOT NULL,
  total numeric(10,2) NOT NULL,
  created_at date NOT NULL
);

INSERT INTO orders
SELECT g,
       (g * 7919) % 2000 + 1,
       'Cust' || lpad(((g * 7919) % 2000 + 1)::text, 4, '0'),
       (ARRAY['pending', 'paid', 'paid', 'paid', 'cancelled'])[(g / 7 + g / 2000 * 3 + g / 13) % 5 + 1],
       round(((g * 37) % 50000) / 100.0 + 5, 2),
       DATE '2026-01-01' + (g / 11 + g / 2000 * 37) % 300      -- trộn số để trạng thái và ngày không phụ thuộc vào khách
FROM generate_series(1, 50000) AS g;

VACUUM ANALYZE orders;

SELECT count(*) AS rows_total,
       count(DISTINCT customer_id) AS customers,
       count(*) FILTER (WHERE customer_id = 42) AS orders_of_42,
       count(*) FILTER (WHERE status = 'paid') AS paid,
       count(*) FILTER (WHERE status = 'pending') AS pending,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

 rows_total | customers | orders_of_42 | paid  | pending | cancelled 
------------+-----------+--------------+-------+---------+-----------
      50000 |      2000 |           25 | 30004 |    9996 |     10000
(1 row)

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 42;

                 QUERY PLAN                  
---------------------------------------------
 Seq Scan on orders (actual rows=25 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 49975
(3 rows)

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE id = 42;

                           QUERY PLAN                           
----------------------------------------------------------------
 Index Scan using orders_pkey on orders (actual rows=1 loops=1)
   Index Cond: (id = 42)
(2 rows)

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=25 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=25
   ->  Bitmap Index Scan on idx_orders_customer (actual rows=25 loops=1)
         Index Cond: (customer_id = 42)
(5 rows)

CREATE INDEX idx_orders_status ON orders (status);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE status = 'paid';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

CREATE INDEX
                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on orders (actual rows=30004 loops=1)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 19996
(3 rows)

                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=5 loops=1)
   Recheck Cond: (status = 'pending'::text)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 9991
   Heap Blocks: exact=417
   ->  Bitmap Index Scan on idx_orders_status (actual rows=9996 loops=1)
         Index Cond: (status = 'pending'::text)
(7 rows)

-- TODO 1: tạo chỉ mục idx_orders_cust_date trên (customer_id, created_at)
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 3;
-- TODO 2: đọc kế hoạch, tìm chữ Backward trong dòng quét chỉ mục

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 3;

CREATE INDEX
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan Backward using idx_orders_cust_date on orders (actual rows=3 loops=1)
         Index Cond: (customer_id = 42)
(3 rows)

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_customer ON orders (customer_id);
\echo SQLSTATE: :LAST_ERROR_SQLSTATE
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_customer_again ON orders (customer_id);
SELECT indexname FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

CREATE INDEX
SQLSTATE: 42P07
CREATE INDEX
CREATE INDEX
         indexname         
---------------------------
 idx_orders_customer
 idx_orders_customer_again
 orders_pkey
(3 rows)

ERROR:  relation "idx_orders_customer" already exists
NOTICE:  relation "idx_orders_customer" already exists, skipping

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (customer_id, status);
CREATE INDEX ON orders (lower(customer));
CREATE INDEX ON orders (customer_id) WHERE status = 'pending';

SELECT indexname FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
           indexname           
-------------------------------
 orders_customer_id_idx
 orders_customer_id_idx1
 orders_customer_id_status_idx
 orders_lower_idx
 orders_pkey
(5 rows)

BEGIN;
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
\echo SQLSTATE: :LAST_ERROR_SQLSTATE
ROLLBACK;
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indrelid = 'orders'::regclass AND NOT indisprimary;

BEGIN
SQLSTATE: 25001
ROLLBACK
CREATE INDEX
     index_name      | indisvalid 
---------------------+------------
 idx_orders_customer | t
(1 row)

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

CREATE INDEX idx_orders_total_desc_last ON orders (total DESC NULLS LAST);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, total FROM orders ORDER BY total DESC LIMIT 3;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, total FROM orders ORDER BY total DESC NULLS LAST LIMIT 3;

CREATE INDEX
                         QUERY PLAN                         
------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Sort (actual rows=3 loops=1)
         Sort Key: total DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on orders (actual rows=50000 loops=1)
(5 rows)

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan using idx_orders_total_desc_last on orders (actual rows=3 loops=1)
(2 rows)

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, total FROM orders WHERE customer_id = 42;

CREATE INDEX
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=25 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=25
   ->  Bitmap Index Scan on idx_orders_customer (actual rows=25 loops=1)
         Index Cond: (customer_id = 42)
(5 rows)

CREATE INDEX idx_orders_customer_total ON orders (customer_id) INCLUDE (total);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, total FROM orders WHERE customer_id = 42;

CREATE INDEX
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Only Scan using idx_orders_customer_total on orders (actual rows=25 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
(3 rows)

CREATE INDEX idx_orders_pending ON orders (customer_id) WHERE status = 'pending';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

CREATE INDEX
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=5 loops=1)
   Recheck Cond: ((customer_id = 42) AND (status = 'pending'::text))
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on idx_orders_pending (actual rows=5 loops=1)
         Index Cond: (customer_id = 42)
(5 rows)

                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on orders (actual rows=14 loops=1)
   Filter: ((customer_id = 42) AND (status = 'paid'::text))
   Rows Removed by Filter: 49986
(3 rows)

CREATE INDEX idx_orders_lower_customer ON orders (lower(customer));
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE lower(customer) = 'cust0042';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer = 'Cust0042';

CREATE INDEX
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=25 loops=1)
   Recheck Cond: (lower(customer) = 'cust0042'::text)
   Heap Blocks: exact=25
   ->  Bitmap Index Scan on idx_orders_lower_customer (actual rows=25 loops=1)
         Index Cond: (lower(customer) = 'cust0042'::text)
(5 rows)

                 QUERY PLAN                  
---------------------------------------------
 Seq Scan on orders (actual rows=25 loops=1)
   Filter: (customer = 'Cust0042'::text)
   Rows Removed by Filter: 49975
(3 rows)

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= DATE '2026-09-01';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE created_at = DATE '2026-09-01';

CREATE INDEX
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=5 loops=1)
   Recheck Cond: ((customer_id = 42) AND (created_at >= '2026-09-01'::date))
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on idx_orders_cust_date (actual rows=5 loops=1)
         Index Cond: ((customer_id = 42) AND (created_at >= '2026-09-01'::date))
(5 rows)

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on orders (actual rows=165 loops=1)
   Filter: (created_at = '2026-09-01'::date)
   Rows Removed by Filter: 49835
(3 rows)

SELECT amname FROM pg_am WHERE amtype = 'i' ORDER BY amname;

 amname 
--------
 brin
 btree
 gin
 gist
 hash
 spgist
(6 rows)

CREATE INDEX idx_orders_btree ON orders USING btree (customer_id);
CREATE INDEX idx_orders_hash ON orders USING hash (customer_id);
CREATE INDEX idx_orders_brin ON orders USING brin (id);

SELECT relname AS index_name, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('orders', 'orders_pkey', 'idx_orders_btree', 'idx_orders_hash', 'idx_orders_brin')
ORDER BY pg_relation_size(oid) DESC;

CREATE INDEX
CREATE INDEX
CREATE INDEX
    index_name    |  size   
------------------+---------
 orders           | 3336 kB
 idx_orders_hash  | 2080 kB
 orders_pkey      | 1112 kB
 idx_orders_btree | 392 kB
 idx_orders_brin  | 24 kB
(5 rows)

SELECT pg_size_pretty(pg_table_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

CREATE INDEX idx_a ON orders (customer_id);
CREATE INDEX idx_b ON orders (created_at);
CREATE INDEX idx_c ON orders (total);
CREATE INDEX idx_d ON orders (status, customer_id);

SELECT pg_size_pretty(pg_table_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

 table_size | indexes_size 
------------+--------------
 3376 kB    | 1112 kB
(1 row)

CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
 table_size | indexes_size 
------------+--------------
 3376 kB    | 3520 kB
(1 row)

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);

CREATE INDEX idx_orders_status_date ON orders (status, created_at);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders WHERE status = 'pending' AND created_at = DATE '2026-09-01';

CREATE INDEX
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=31 loops=1)
   Recheck Cond: ((status = 'pending'::text) AND (created_at = '2026-09-01'::date))
   Heap Blocks: exact=10
   ->  Bitmap Index Scan on idx_orders_status_date (actual rows=31 loops=1)
         Index Cond: ((status = 'pending'::text) AND (created_at = '2026-09-01'::date))
(5 rows)

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 3;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at ASC LIMIT 3;

CREATE INDEX
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan using idx_orders_cust_date on orders (actual rows=3 loops=1)
         Index Cond: (customer_id = 42)
(3 rows)

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan Backward using idx_orders_cust_date on orders (actual rows=3 loops=1)
         Index Cond: (customer_id = 42)
(3 rows)