The Problem: Unused Composite Indexes in GridDB TQL

When working with large-scale time-series or IoT data in GridDB, performance is everything. A common optimization strategy is to create a composite (multi-column) index on columns like deviceId and timestamp to speed up range queries. However, many developers notice that when they execute these queries using TQL (Transaction Query Language) via the Python client, the execution time remains unchanged—indicating that GridDB is performing a full container scan instead of utilizing the index.

If you have verified that your index exists, restarted your client, and reordered your query predicates to no avail, the issue lies in a fundamental architectural distinction within GridDB: the difference between the TQL Engine and the SQL Engine.

The Root Cause: TQL vs. SQL Query Engines

GridDB provides two distinct interfaces for querying data, each powered by a different execution engine:

  • TQL (NoSQL API): Executed via container.query(). TQL is designed for ultra-low latency, high-throughput key-value operations and simple single-column searches. Its query optimizer is lightweight and does not support composite index optimization for complex range scans.
  • SQL API: Executed at the Store level (e.g., store.query()). GridDB's SQL engine features a sophisticated, cost-based optimizer (CBO) capable of analyzing query plans and properly utilizing multi-column composite indexes.

Because you are executing your query via container.query() (TQL syntax), GridDB's lightweight TQL optimizer ignores the composite index and falls back to a full-table scan.

How to Fix It

Solution 1: Switch to the GridDB SQL API

To leverage your composite index, you should bypass the container-specific TQL interface and run your query using the Store-level SQL interface. Here is how to rewrite your Python code to use the SQL API:

# Assuming 'store' is your GridStore object and 'my_device_container' is your container name
sql_query = """
SELECT * 
FROM my_device_container 
WHERE deviceId = 'dev42' 
AND timestamp > TIMESTAMP('2026-01-01T00:00:00Z')
"""

# Execute using store.query (SQL) instead of container.query (TQL)
query = store.query(sql_query)
rs = query.fetch()

for row in rs:
    # Process your rows here
    pass

By querying the store directly and specifying the container name in the FROM clause, GridDB routes the query through its SQL optimizer, which will recognize and utilize your composite index on ["deviceId", "timestamp"].

Solution 2: Use Single-Column TREE Indexes for TQL

If your architecture requires you to use the NoSQL TQL API (container.query()), you must adapt to TQL's indexing limitations. TQL works best with single-column TREE indexes.

Instead of a composite index, create a single-column index on your most selective column (usually deviceId if you have thousands of devices):

# Create a single index on deviceId
container.create_index("deviceId", griddb.IndexType.TREE)

When you run your TQL query, the engine will use the index to instantly narrow down the rows to only those matching deviceId='dev42', and then filter the timestamp condition in memory. For 8 million rows distributed across multiple devices, this approach still provides a massive performance boost over a full scan.

Solution 3: Ensure the Index Type is TREE (Not HASH)

If you are performing range queries (using operators like >, <, or BETWEEN), ensure your index type is set to TREE. A HASH index only supports exact match (=) lookups and will be completely ignored by the query planner for any inequality or range conditions.

Summary of Best Practices

  • Use SQL (Store-level queries) when you need complex query optimization, joins, or multi-column composite index support.
  • Use TQL (Container-level queries) for high-speed, single-key lookups or simple single-column index queries.
  • Always prefer TREE indexes over HASH indexes if your queries involve time-range filtering.