Indexes
Indexes in a database are designed to increase the speed at which the database management system (DBMS) can locate and retrieve the desired data from a table or a materialized view.
Indexes are usually created on one or more columns in a table and can greatly improve the performance of queries, particularly for tables that are large in size or accessed frequently. Indexes are usually created on non-primary columns to speed up point-select queries on those columns.
Benefits of using indexes
In general, using indexes can significantly enhance the performance and efficiency of the system.
When to use indexes
Indexes can be particularly useful for optimizing the performance of queries that retrieve a small number of records from a large dataset. In RisingWave, indexes can speed up batch queries.
How to use indexes
You can use the CREATE INDEX command to construct an index on a table or a materialized view. The syntax is as follows:
Here is a simple example. Let’s create two tables, customers
and orders
.
If you want to speed up the query of fetching a customer record by the phone number, you can build an index on the c_phone
column in the customers
table.
If you want to speed up the query of fetching all the orders of a customer by the customer key, you can build an index on the o_custkey
column in the orders
table.
How to decide which columns to include?
By default, RisingWave creates an index that includes all columns of a table or a materialized view if you omit the INCLUDE
clause. This differs from the standard PostgreSQL. This is because RisingWave’s design as a cloud-native streaming database includes several key differences from PostgreSQL, including the use of an object store for more cost-effective storage, and the desire to make index creation as simple as possible for users who are not experienced with database systems.
By including all columns, RisingWave ensures that an index will cover all of the columns touched by a query and eliminates the need for a primary table lookup, which can be slower in a cloud environment due to network communication. However, RisingWave still provides the option to include only specific columns using the INCLUDE
clause for users who wish to do so.
For example:
If your queries only access certain columns, you can create an index that includes only those columns. The RisingWave optimizer will automatically select the appropriate index for your query.
TIP
You can use the EXPLAIN command to view the execution plan.
How to decide the index distribution key?
RisingWave will use the first index column as the distributed_column
by default if you omit the DISTRIBUTED BY
clause. RisingWave distributes the data across multiple nodes and uses the distributed_column
to determine how to distribute the data based on the index. If your queries intend to use indexes but only provide the prefix of the index_column
, it could be a problem for RisingWave to determine which node to access the index data from. To address this issue, you can specify the distributed_column
yourself, ensuring that these columns are the prefixes of the index_column
.
For example:
Indexes on expressions
RisingWave supports creating indexes on expressions. Indexes on expressions are normally used to improve the performance of queries for frequently used expressions. To create an index on an expression, use the syntax:
For example, if you often perform queries like this:
Then you might want to create an index like the following to improve the performance of such queries:
This syntax is quite useful when working with a semi-structured table that utilizes the JSONB datatype. Here is an example of creating an index on a specific field within a JSONB column.
See also
Was this page helpful?