Optimize queries
Limited availability
InfluxDB Clustered is currently only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.
Use the following tools to help you identify performance bottlenecks and troubleshoot problems in queries:
EXPLAIN and ANALYZE
To view the query engine’s execution plan and metrics for an SQL query, prepend EXPLAIN or EXPLAIN ANALYZE to the query.
The report can reveal query bottlenecks such as a large number of table scans or parquet files, and can help triage the question, “Is the query slow due to the amount of work required or due to a problem with the schema, compactor, etc.?”
The following example shows how to use the InfluxDB v3 Python client library and pandas to view EXPLAIN and EXPLAIN ANALYZE results for a query:
from influxdb_client_3 import InfluxDBClient3
import pandas as pd
import tabulate # Required for pandas.to_markdown()
# Instantiate an InfluxDB client.
client = InfluxDBClient3(token = f"DATABASE_TOKEN",
host = f"cluster-host.com",
database = f"DATABASE_NAME")
sql_explain = '''EXPLAIN
SELECT temp
FROM home
WHERE time >= now() - INTERVAL '90 days'
AND room = 'Kitchen'
ORDER BY time'''
table = client.query(sql_explain)
df = table.to_pandas()
print(df.to_markdown(index=False))
assert df.shape == (2, 2), f'Expect {df.shape} to have 2 columns, 2 rows'
assert 'physical_plan' in df.plan_type.values, "Expect physical_plan"
assert 'logical_plan' in df.plan_type.values, "Expect logical_plan"
sql_explain_analyze = '''EXPLAIN ANALYZE
SELECT *
FROM home
WHERE time >= now() - INTERVAL '90 days'
ORDER BY time'''
table = client.query(sql_explain_analyze)
df = table.to_pandas()
print(df.to_markdown(index=False))
assert df.shape == (1,2)
assert 'Plan with Metrics' in df.plan_type.values, "Expect plan metrics"
client.close()
Replace the following:
DATABASE_NAME: your InfluxDB Clustered databaseDATABASE_TOKEN: a database token with sufficient permissions to the specified database
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.