B-tree indexes are the most frequently used indexes. They are suitable for equality queries, range queries, index sorting, multicolumn conditions, and conditional expressions.

Operators

Operator Example
< select * from test where id <1
<= select * from test where id <=1
= select * from test where id =1
>= select * from test where id >=1
> select * from test where id >1
between and select * from test where id between 1 and 10
in select * from test where id in (1,2,3)
like select * from test where id like 'abc%'

Multicolumn indexes

A multicolumn index indicates an index on multiple columns of a table. A maximum of 32 columns can be indexed.

  • Create a table that has the following schema.
    create table test(id int,name varchar(10));
  • Query data.
    select * from test where id=1 and name='a1';         
  • Create a multicolumn index.
    create index ON test(id,name);
  • View the execution plan.
    postgres=# 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)

In the following sample code, no indexes are used to query data if the conditions do not include IDs.

postgres=# 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)

Indexes on expressions

For indexes on expressions, an index column does not need to be a column of the physical table, but can be a function or an expression that is computed from one or more columns of the table.

  • Create a table that has the following schema.
    create table test(id int,name varchar(10));
  • Query data.
    select * from test where lower(name)='a1';
  • Create an index on an expression.
    create index ON test (lower(name));
  • View the execution plan.
    postgres=# 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)

The cost of maintaining indexes on expressions is high, because new expressions must be computed every time when a row of data is inserted or updated.

Partial indexes

A partial index is an index that is created based on a subset of a table. The subset is defined by a conditional expression. The index contains entries for only table rows that satisfy the predicate.

  • Create a table that has the following schema.
    create table test(id int,name varchar(10));
  • Query data.
    select * from test where name='a1';
    select * from test where name='a2';
  • Create a partial index.
    create index ON test(name) where name='a1';
  • View the execution plan.
    postgres=# 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)
    
    postgres=# 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

Indexes can find the rows to be returned by a query, and return the rows in a specific sorted order. This process does not require separate sorting steps.

  • Create a table that has the following schema.
    create table test(id int,name varchar(10));
  • Query data.
    select * from test order by name desc;
  • View the execution plan before an index is created.
    postgres=# 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 the index.
    create index ON test (name desc);
  • View the execution plan.
    postgres=# 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)

By default, a B-tree index stores its entries in ascending order and places its null values at the end. When you create B-tree indexes, you can use the ASC, DESC, NULLS FIRST, and NULLS LAST options to change the sorting order.

Index-only scans and covering indexes

If you query only index-related fields, index-only scans can return the results from indexes alone, but do not need to access specific data files.

  • Example 1
    • Create a table that has the following schema.
      create table test(id int,name varchar(10));
    • Query data.
      select name from test where name='a1';
    • View the execution plan before an index is created.
      postgres=# explain select name from test where name='a1';
                            QUERY PLAN
      -------------------------------------------------------
       Seq Scan on test  (cost=0.00..1791.00 rows=1 width=6)
         Filter: ((name)::text = 'a1'::text)
      (3 rows)
    • Create the index.
      create index ON test (name);
    • View the execution plan after the index is created.
      postgres=# 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)
  • Example 2
    • Create a table that has the same schema as the table created in Example 1.
      create table test(id int,name varchar(10));
    • Query data.
      select * from test where name='a1';
    • View the execution plan before an index is created.
      postgres=# 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)
    • Create a covering index.
      create index ON test (name) include(id);
    • View the execution plan after the index is created.
      postgres=# 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)

You can use a covering index only when your query statement references only the columns stored in an index. After the covering index is used, the system scans only the index and does not need to scan data in the table to return results.

Types of index pages

An index page provides brief introduction of the internal architecture of an index. The preceding indexing features are implemented based on the internal architecture. B-tree index pages of PolarDB are classified into the following types:

  • meta page
  • root page
  • branch page
  • leaf page

The meta page and root page types are required. A meta page is stored in one page and is directed to the page ID of a root page. A root page may be insufficient to store all the heap items as records are increasing. Therefore, a leaf page, a branch page, or a multi-layer branch page is required.

B-tree indexes