SEARCH_INDEXES_BY_ORGANIZATION view

BigQuery search indexes provide free index management until your organization reaches the limit in a given region. You can use the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view to understand your current consumption towards that limit, broken down by projects and tables. The INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view contains one row for each search index for the whole organization associated with the current project.

Required permissions

To query the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view, you need the following Identity and Access Management (IAM) permissions for your organization:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

  • roles/bigquery.admin
  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer

This schema view is only available to users with defined Google Cloud organizations.

For more information about BigQuery permissions, see Access control with IAM.

Schema

The INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view has the following schema:

Column name Data type Value
project_id STRING The name of the project that contains the dataset.
project_number STRING The project number that contains the dataset.
index_catalog STRING The name of the project that contains the dataset.
index_schema STRING The name of the dataset that contains the index.
table_name STRING The name of the base table that the index is created on.
index_name STRING The name of the search index.
index_status STRING The status of the index can be one of the following:
  • ACTIVE: the index is usable or being created.
  • PENDING DISABLEMENT: the total size of indexed base tables exceeds your organization's limit; the index is queued for deletion. While in this state, the index is usable in search queries and you are charged for the search index storage.
  • TEMPORARILY DISABLED: either the total size of indexed base tables exceeds your organization's limit, or the base indexed table is smaller than 10 GB. While in this state, the index is not used in search queries and you are not charged for the search index storage.
  • PERMANENTLY DISABLED: there is an incompatible schema change on the base table, such as changing the type of an indexed column from STRING to INT64.
index_status_details RECORD The record contains following fields:
  • throttle_status: indicates the throttle status of the search index, possible values are as follows:
    • UNTHROTTLED: the index is usable.
    • BASE_TABLE_TOO_SMALL: the base table size is smaller than 10 GB. This limit applies whether or not you use your own reservation for your index-management jobs. In this case, the index is temporarily disabled and search queries don't use the index.
    • BASE_TABLE_TOO_LARGE: the base table size exceeds your organization's limit.
    • ORGANIZATION_LIMIT_EXCEEDED: the total size of indexed base tables in your organization exceeds your organization's limit.
  • message: detailed message that describes the index status.
use_background_reservation BOOL Indicates whether the index maintenance uses the BACKGROUND reservation. This is set to FALSE when the index maintenance uses the limit.

Scope and syntax

Queries against this view must include a region qualifier. The following table explains the region scope for this view:

View name Resource scope Region scope
[`PROJECT_ID`.]`region-REGION`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION Organization that contains the specified project REGION

Replace the following:

  • Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

  • REGION: the region for your project. For example, `myproject`.`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION.

Index throttling

If an index is throttled, its table size is not counted towards your organization's limit. This throttling occurs when the base table size falls under 10 GB or exceeds your organization's limit. When an index is throttled, its management jobs are paused, causing the index to become stale and eventually temporarily disabled. Consequently, search queries are unable to use the index.

Examples

This section includes example queries of the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view.

Find if the consumption exceeds the limit in a given region

The following example illustrates if the total indexed base table size across an organization, utilizing shared slots within the US multi-region, exceeds 100 TB:

WITH
 indexed_base_table_size AS (
 SELECT
   SUM(base_table.total_logical_bytes) AS total_logical_bytes
 FROM
   `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
 JOIN
   `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
 ON
   (search_index.table_name = base_table.table_name
     AND search_index.project_id = base_table.project_id
     AND search_index.index_schema = base_table.table_schema)
 WHERE
   TRUE
   -- Excludes search indexes that are permanently disabled.
   AND search_index.index_status != 'PERMANENTLY DISABLED'
   -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
   -- less than 10 GB. These tables don't count toward the limit.
   AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
   -- Excludes search indexes whose project has BACKGROUND reservation purchased
   -- for search indexes.
   AND search_index.use_background_reservation = false
 -- Outputs the total indexed base table size if it exceeds 100 TB,
 -- otherwise, doesn't return any output.
)
SELECT * FROM indexed_base_table_size
WHERE total_logical_bytes >= 109951162777600 -- 100 TB

The result is similar to the following:

+---------------------+
| total_logical_bytes |
+---------------------+
|     109951162777601 |
+---------------------+

Find total indexed base table size by projects in a region

The following example gives the breakdown on each project in a US multi-region with the total size of indexed base tables:

SELECT
 search_index.project_id,
 search_index.use_background_reservation,
 SUM(base_table.total_logical_bytes) AS total_logical_bytes
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
JOIN
 `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
ON
 (search_index.table_name = base_table.table_name
   AND search_index.project_id = base_table.project_id
   AND search_index.index_schema = base_table.table_schema)
WHERE
 TRUE
  -- Excludes search indexes that are permanently disabled.
  AND search_index.index_status != 'PERMANENTLY DISABLED'
  -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
  -- less than 10 GB. These tables don't count toward limit.
 AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
GROUP BY search_index.project_id, search_index.use_background_reservation

The result is similar to the following:

+---------------------+----------------------------+---------------------+
|     project_id      | use_background_reservation | total_logical_bytes |
+---------------------+----------------------------+---------------------+
| projecta            |     true                   |     971329178274633 |
+---------------------+----------------------------+---------------------+
| projectb            |     false                  |     834638211024843 |
+---------------------+----------------------------+---------------------+
| projectc            |     false                  |     562910385625126 |
+---------------------+----------------------------+---------------------+

Find throttled search indexes

This following example returns all search indexes that are throttled within the organization and region:

SELECT project_id, index_schema, table_name, index_name
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION
WHERE
 -- Excludes search indexes that are permanently disabled.
 index_status != 'PERMANENTLY DISABLED'
 AND index_status_details.throttle_status IN ('ORGANIZATION_LIMIT_EXCEEDED', 'BASE_TABLE_TOO_LARGE')

The result is similar to the following:

+--------------------+--------------------+---------------+----------------+
|     project_id     |    index_schema    |  table_name   |   index_name   |
+--------------------+--------------------+---------------+----------------+
|     projecta       |     dataset_us     |   table1      |    index1      |
|     projectb       |     dataset_us     |   table1      |    index1      |
+--------------------+--------------------+---------------+----------------+