2 Ways to Check if a Table has any Partial Indexes in SQLite

by

In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.

If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.

Option 1: Use PRAGMA index_list to Identify Partial Indexes

To determine if a table has any partial indexes, you can now use the PRAGMA index_list() command, which includes a partial column to indicate whether the index is partial or not.

Syntax

The syntax goes something like this:

PRAGMA schema.index_list('table_name');

This command will return a list of indexes on the specified table, along with several columns:

  • seq: Sequence number of the index.
  • name: Name of the index.
  • unique: Indicates if the index is unique (1 if unique, 0 if not).
  • origin: Shows how the index was created (c for constraint, u for unique, etc.).
  • partial: Indicates if the index is partial (1 if partial, 0 if full).

If the partial column for an index has a value of 1, it means the index is a partial index. Otherwise, it is a full index.

Example: Checking for Partial Indexes

Let’s check for partial indexes in a table named orders:

PRAGMA index_list('orders');

Suppose the output is as follows:

+-----+--------------------+--------+--------+---------+
| seq |        name        | unique | origin | partial |
+-----+--------------------+--------+--------+---------+
| 0   | idx_all_orders     | 0      | c      | 0       |
| 1   | idx_pending_orders | 0      | c      | 1       |
+-----+--------------------+--------+--------+---------+

In this example:

  • idx_all_orders is a full index because partial is 0.
  • idx_pending_orders is a partial index because partial is 1.

This output confirms that the orders table has one partial index (idx_pending_orders), which is defined with a condition, likely something like WHERE status = 'pending' in its creation SQL.

Option 2: Query the sqlite_master Table

Another way to do it is to query the sqlite_master table. While this table doesn’t have a partial column (to indicate whether it’s a partial index or not), it does have a sql column, which shows us the SQL code used to create the index.

Therefore, we can include this column in our query to show us the actual CREATE INDEX statement. If it has a WHERE clause, then it’s a partial index.

Example:

SELECT 
    name,
    sql
FROM sqlite_master 
WHERE type = 'index'
AND tbl_name = 'orders';

Output:

+--------------------+-------------------------------------------------------+
|        name        |                          sql                          |
+--------------------+-------------------------------------------------------+
| idx_pending_orders | CREATE INDEX idx_pending_orders ON orders(order_date) |
|                    | WHERE status = 'pending'                              |
+--------------------+-------------------------------------------------------+
| idx_all_orders     | CREATE INDEX idx_all_orders ON orders(order_date)     |
+--------------------+-------------------------------------------------------+

So again, we can see that the idx_pending_orders index is a partial index, due to the fact that it includes a WHERE clause in its definition.

Summary

Here it is in a nutshell:

  • The PRAGMA index_list() command includes a partial column that contains 1 for partial indexes, and 0 for full indexes.
  • The sqlite_master table provides us the index’s definition, which, if it includes a WHERE clause, tells us that it’s a partial index.