PolarDB:B-TREE indexes

Supported operators

B-tree indexes accelerate queries that use the following comparison operators:

Multicolumn indexes

A multicolumn index covers multiple columns in a single index structure. PolarDB supports up to 32 columns per index.

-- Create a table
CREATE TABLE test(id int, name varchar(10));

-- Create a multicolumn index on (id, name)
CREATE INDEX ON test(id, name);

When both columns appear in the query condition, the planner uses the index:

EXPLAIN SELECT * FROM test WHERE id = 1 AND name = 'a1';

                                       QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using a_id_name_idx on test  (cost=0.42..8.44 rows=1 width=10)
   Index Cond: ((id = 1) AND (name = 'a1'::text))
(2 rows)

If the query omits the leading column (id), the planner falls back to a sequential scan:

EXPLAIN SELECT * FROM test WHERE name = 'a1';

                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=1 width=10)
   Filter: ((name)::text = 'a1'::text)
(2 rows)

How the leftmost-column rule works: A multicolumn index is most effective when query conditions include equality constraints on the leading (leftmost) columns. Constraints on later columns are applied only after the planner narrows down rows using the leading columns. If the leading column is absent from the query, the index cannot limit the scan range.

Indexes on expressions

An expression index lets you index the result of a function or expression rather than a raw column value. This is useful when queries consistently apply a transformation before filtering.

-- Without an index, this query does a sequential scan
SELECT * FROM test WHERE lower(name) = 'a1';

-- Create an index on the expression
CREATE INDEX ON test(lower(name));

After creating the index, the planner uses a bitmap scan:

EXPLAIN SELECT * FROM test WHERE lower(name) = 'a1';

                                           QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=12.17..571.91 rows=500 width=10)
   Recheck Cond: (lower((name)::text) = 'a1'::text)
   ->  Bitmap Index Scan on test_lower_idx  (cost=0.00..12.04 rows=500 width=0)
         Index Cond: (lower((name)::text) = 'a1'::text)
(4 rows)

Performance trade-off: Every time a row is inserted or updated, PolarDB recomputes the indexed expression. Use expression indexes when read performance is the priority and write throughput is acceptable.

Partial indexes

A partial index covers only the rows that satisfy a predicate. This reduces index size and improves write performance for tables where queries target a predictable subset of rows.

-- Create an index that only covers rows where name = 'a1'
CREATE INDEX ON test(name) WHERE name = 'a1';

Queries that match the predicate use the index:

EXPLAIN SELECT * FROM test WHERE name = 'a1';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using test_name_idx on test  (cost=0.12..8.14 rows=1 width=10)
(1 row)

Queries that do not match the predicate fall back to a sequential scan:

EXPLAIN SELECT * FROM test WHERE name = 'a2';

                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=1 width=10)
   Filter: ((name)::text = 'a2'::text)
(2 rows)

Index sorting

B-tree indexes store entries in ascending order with null values at the end by default. Because the index already maintains a sort order, queries with ORDER BY can read rows directly from the index without a separate sort step.

-- Before creating an index, the planner sorts explicitly
EXPLAIN SELECT * FROM test ORDER BY name DESC;

                                QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=9845.82..10095.82 rows=100000 width=10)
   Sort Key: name DESC
   ->  Seq Scan on test  (cost=0.00..1541.00 rows=100000 width=10)
(3 rows)

-- Create an index that stores entries in descending order
CREATE INDEX ON test(name DESC);

-- After creating the index, the planner reads directly from the index
EXPLAIN SELECT * FROM test ORDER BY name DESC;

                                        QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using test_name_idx on test  (cost=0.29..3666.46 rows=100000 width=10)
(1 row)

Use ASC, DESC, NULLS FIRST, and NULLS LAST to control the sort order when creating the index.

Index-only scans and covering indexes

When a query references only the columns stored in an index, PolarDB can return results from the index alone without accessing the underlying table data. This is called an index-only scan.

-- Create a table and an index
CREATE TABLE test(id int, name varchar(10));
CREATE INDEX ON test(name);

EXPLAIN SELECT name FROM test WHERE name = 'a1';

                                        QUERY PLAN
-------------------------------------------------------------------------------
 Index Only Scan using test_name_idx on test  (cost=0.29..8.31 rows=1 width=6)
   Index Cond: (name = 'a1'::text)
(2 rows)

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=1 width=6)
   Filter: ((name)::text = 'a1'::text)
(3 rows)

-- Add the 'id' column to the index so SELECT * queries can use an index-only scan
CREATE INDEX ON test(name) INCLUDE(id);

EXPLAIN SELECT * FROM test WHERE name = 'a1';

                                            QUERY PLAN
-----------------------------------------------------------------------------------
 Index Only Scan using test_name_id_idx on test  (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (name = 'a1'::text)
(2 rows)

Internal architecture

PolarDB B-tree indexes use four types of pages internally:

The meta page and root page are always present. As the number of indexed rows grows, a root page may become insufficient to store all heap items. Therefore, leaf pages, branch pages, or multi-layer branch pages are added to maintain balanced access paths.