Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.ditto.live/llms.txt

Use this file to discover all available pages before exploring further.

The PROFILE statement executes a SELECT query and appends detailed profiling information to the result set, including the query execution plan annotated with document counts and timing data.
Profiling information is stored in the virtual collection system:completed_requests. You can also query profiling and request history data directly using the virtual collections system:active_requests and system:request_history. See Virtual Collections for more details.

​
Syntax

DQL
PROFILE select_statement
PROFILE Syntax Diagram

​
How It Works

When you execute a PROFILE statement:
  1. The query runs normally and returns all result documents
  2. An additional result is appended containing the system:completed_requests cache entry
  3. This entry includes the query plan with profiling information (document counts, timing)

​
Using PROFILE

DQL
PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020
This executes the query and returns:
  • All matching documents
  • A final result containing the annotated query plan with profiling data

​
Profiling Information

The profiling result includes:
  • Document counts: How many documents were processed at each step of the plan
  • Timing information: How long each operation took
  • Query plan structure: The full execution plan showing scans, filters, projections, etc.
  • Index usage: Which indices (if any) were used

​
Example Output Structure

The profiling information is returned as the final result in the result set and typically includes: 
{
  "plan": {
    "#operator": "project",
    "document_count": 42,
    "elapsed_time_ms": 5.2,
    "children": [
      {
        "#operator": "filter",
        "document_count": 42,
        "elapsed_time_ms": 2.1,
        "children": [
          {
            "#operator": "indexScan",
            "index": "ix_color_year",
            "document_count": 50,
            "elapsed_time_ms": 1.5
          }
        ]
      }
    ]
  }
}

​
Using Profiling Data

Profiling data helps you:
  1. Identify bottlenecks: See which operations are taking the most time
  2. Verify index usage: Confirm your query is using the expected indices
  3. Understand query execution: See how the planner executes your query
  4. Optimize performance: Make informed decisions about index creation and query structure

​
Code Examples

let results = await ditto.store.execute(
  query: "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
)

// The last item in results will contain the profiling information
for (index, item) in results.enumerated() {
  if index == results.count - 1 {
    // This is the profiling data
    print("Profiling info: \(item)")
  } else {
    // These are regular result documents
    print("Result: \(item)")
  }
}

​
PROFILE vs #profile Directive

The PROFILE statement is equivalent to using the #profile directive:
DQL
-- These are equivalent
PROFILE SELECT * FROM cars WHERE color = 'blue'

/*+ {"#profile": true} */
SELECT * FROM cars WHERE color = 'blue'

​
Limitations

  • PROFILE only supports SELECT statements
  • The profiling data is appended as the last result, so you need to handle it separately from your query results
  • Profiling may add slight overhead to query execution

​
See Also

  • EXPLAIN - View query execution plans without running the query
  • Directives - Control query planner behavior
  • Access Paths - Understanding query execution strategies
  • Indexing - Creating indices to improve query performance