SQLite Table Scans with json_tree and Index Usage

SQLite Query Plan Indicates Full Table Scan on jsondumps

When analyzing the provided SQLite query plan, the term SCAN TABLE jsondumps suggests that the database engine is performing a full table scan on the jsondumps table. This behavior occurs despite the presence of an index on the fileSampleId column. A full table scan means that SQLite is reading every row in the jsondumps table to evaluate the query conditions, which can be inefficient for large datasets.

The query in question involves joining the jsondumps table with the FileSamplings and AttrKeys tables, as well as using the json_tree function to parse JSON content stored in the jsondumps.content column. The query plan indicates that SQLite is scanning the jsondumps table, searching the FileSamplings table using its primary key, scanning the json_tree virtual table, and searching the AttrKeys table using an index. The presence of a full table scan on jsondumps raises questions about index utilization and query optimization.

Interplay Between json_tree and Index Usage Leading to Full Scans

The root cause of the full table scan on the jsondumps table lies in the interaction between the json_tree function and the way SQLite optimizes queries involving JSON data. The json_tree function is a virtual table that recursively extracts data from a JSON object. When used in a query, SQLite must evaluate the JSON content for each row in the jsondumps table, which complicates the use of traditional indexes.

The jsondumps table has an index on the fileSampleId column, but this index is not directly helpful for filtering rows based on the JSON content. The query includes conditions on the type field within the JSON (type = 'real' or type = 'integer'), which cannot be efficiently evaluated using the existing index. As a result, SQLite opts for a full table scan to ensure all rows are processed.

Additionally, the join condition jsondumps.fileSampleId = FileSamplings.ID is evaluated after the JSON content is parsed, which further limits the optimizer’s ability to leverage the index on fileSampleId. The query plan shows that SQLite is using the primary key index for the FileSamplings table and a covering index for the AttrKeys table, but the jsondumps table remains a bottleneck due to the JSON processing overhead.

Optimizing json_tree Queries with Indexes and Schema Design

To address the full table scan issue, several strategies can be employed to optimize the query and improve performance. These strategies focus on reducing the need for full table scans by leveraging indexes, restructuring the schema, and optimizing the query itself.

1. Materializing JSON Data into Relational Columns

One effective approach is to materialize frequently accessed JSON fields into separate relational columns. For example, if the type field is often queried, it can be extracted into a dedicated column in the jsondumps table. This allows SQLite to use indexes on these columns for filtering, avoiding the need to parse JSON content for every row.

ALTER TABLE jsondumps ADD COLUMN type TEXT;
UPDATE jsondumps SET type = json_extract(content, '$.type');
CREATE INDEX JSONDUMPS_TYPE ON jsondumps(type);

With this change, the query can be rewritten to filter on the type column directly, enabling the use of the new index:

SELECT fileSamplings.ID AS fileSamplingsId, AttrKeys.ID AS AttrKeysId, json_tree.atom AS numval
FROM FileSamplings
JOIN jsondumps ON jsondumps.fileSampleId = FileSamplings.ID
JOIN AttrKeys ON AttrKeys.name = json_tree.fullkey
WHERE (jsondumps.type = 'real' OR jsondumps.type = 'integer')
  AND FileSamplings.sampleId = 46;

2. Using Generated Columns for JSON Fields

SQLite supports generated columns, which can automatically extract and store JSON fields. This approach combines the flexibility of JSON with the performance benefits of relational columns.

ALTER TABLE jsondumps ADD COLUMN type TEXT GENERATED ALWAYS AS (json_extract(content, '$.type')) VIRTUAL;
CREATE INDEX JSONDUMPS_TYPE ON jsondumps(type);

The query can then use the generated column for filtering, similar to the previous example.

3. Leveraging Partial Indexes

If only a subset of the jsondumps table is frequently queried, a partial index can be created to index only the relevant rows. For example, if the query often filters on FileSamplings.sampleId = 46, a partial index can be created for this specific condition.

CREATE INDEX JSONDUMPS_FILESAMPLEID_46 ON jsondumps(fileSampleId) WHERE FileSamplings.sampleId = 46;

This index can significantly reduce the number of rows scanned during query execution.

4. Query Restructuring for Better Index Utilization

The original query can be restructured to improve index usage. For example, the join order can be adjusted to prioritize tables with selective filters, reducing the number of rows processed in subsequent joins.

SELECT fileSamplings.ID AS fileSamplingsId, AttrKeys.ID AS AttrKeysId, json_tree.atom AS numval
FROM FileSamplings
JOIN jsondumps ON jsondumps.fileSampleId = FileSamplings.ID
JOIN AttrKeys ON AttrKeys.name = json_tree.fullkey
WHERE FileSamplings.sampleId = 46
  AND (json_extract(jsondumps.content, '$.type') IN ('real', 'integer');

This version of the query ensures that the FileSamplings table is filtered first, reducing the number of rows joined with jsondumps.

5. Using Covering Indexes

A covering index includes all the columns needed by the query, eliminating the need to access the base table. For the jsondumps table, a covering index can be created to include both fileSampleId and content.

CREATE INDEX JSONDUMPS_COVERING ON jsondumps(fileSampleId, content);

This index allows SQLite to retrieve the required data directly from the index, avoiding a full table scan.

6. Analyzing and Optimizing JSON Structure

If the JSON structure is complex or deeply nested, consider simplifying it to reduce parsing overhead. For example, flattening the JSON hierarchy or reducing the number of nested objects can improve query performance.

7. Monitoring and Profiling Query Performance

Use SQLite’s EXPLAIN QUERY PLAN and ANALYZE commands to monitor and profile query performance. These tools provide insights into how SQLite executes queries and can help identify bottlenecks.

EXPLAIN QUERY PLAN
SELECT fileSamplings.ID AS fileSamplingsId, AttrKeys.ID AS AttrKeysId, json_tree.atom AS numval
FROM FileSamplings
JOIN jsondumps ON jsondumps.fileSampleId = FileSamplings.ID
JOIN AttrKeys ON AttrKeys.name = json_tree.fullkey
WHERE FileSamplings.sampleId = 46
  AND (json_extract(jsondumps.content, '$.type') IN ('real', 'integer'));

8. Considering Alternative Database Features

If JSON processing remains a bottleneck, consider using SQLite’s JSON1 extension or exploring alternative database systems optimized for JSON data, such as PostgreSQL with its jsonb type.

By implementing these strategies, the full table scan on the jsondumps table can be mitigated, leading to improved query performance and more efficient use of indexes. Each approach should be evaluated based on the specific requirements and constraints of the application to determine the most effective solution.