> ## 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.

# PROFILE

> The PROFILE statement helps you analyze query performance by providing an annotated query plan with execution statistics.

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.

<Note>
  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](/dql/virtual-collections#systemactive_requests--systemrequest_history) for more details.
</Note>

## Syntax

```sql DQL theme={null}
PROFILE select_statement
```

<img src="https://mintcdn.com/ditto-248bc0d1/pf3IlRElveI85RoM/images/dql/Profile.svg?fit=max&auto=format&n=pf3IlRElveI85RoM&q=85&s=ee98d641cbd05ff69249ff900ead18a8" alt="PROFILE Syntax Diagram" width="217" height="77" data-path="images/dql/Profile.svg" />

## 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

```sql DQL theme={null}
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:

```json theme={null}
{
  "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

<CodeGroup>
  ```swift Swift theme={null}
  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)")
    }
  }
  ```

  ```kotlin Kotlin theme={null}
  val results = ditto.store.execute(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  )

  // The last item in results will contain the profiling information
  results.forEachIndexed { index, item ->
    if (index == results.size - 1) {
      // This is the profiling data
      println("Profiling info: $item")
    } else {
      // These are regular result documents
      println("Result: $item")
    }
  }
  ```

  ```javascript JS theme={null}
  const results = await ditto.store.execute(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  );

  // The last item in results will contain the profiling information
  results.forEach((item, index) => {
    if (index === results.length - 1) {
      // This is the profiling data
      console.log("Profiling info:", item);
    } else {
      // These are regular result documents
      console.log("Result:", item);
    }
  });
  ```

  ```java Java theme={null}
  var results = ditto.store.execute(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  );

  // The last item in results will contain the profiling information
  for (int i = 0; i < results.size(); i++) {
    if (i == results.size() - 1) {
      // This is the profiling data
      System.out.println("Profiling info: " + results.get(i));
    } else {
      // These are regular result documents
      System.out.println("Result: " + results.get(i));
    }
  }
  ```

  ```csharp C# theme={null}
  var results = await ditto.Store.ExecuteAsync(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  );

  // The last item in results will contain the profiling information
  for (int i = 0; i < results.Count; i++) {
    if (i == results.Count - 1) {
      // This is the profiling data
      Console.WriteLine($"Profiling info: {results[i]}");
    } else {
      // These are regular result documents
      Console.WriteLine($"Result: {results[i]}");
    }
  }
  ```

  ```cpp C++ theme={null}
  auto results = ditto.get_store().execute(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  ).get();

  // The last item in results will contain the profiling information
  for (size_t i = 0; i < results.size(); i++) {
    if (i == results.size() - 1) {
      // This is the profiling data
      std::cout << "Profiling info: " << results[i] << std::endl;
    } else {
      // These are regular result documents
      std::cout << "Result: " << results[i] << std::endl;
    }
  }
  ```

  ```rust Rust theme={null}
  let results = ditto.store()
      .execute_v2((
          "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020",
          serde_json::json!({})
      ))
      .await?;

  // The last item in results will contain the profiling information
  for (index, item) in results.iter().enumerate() {
    if index == results.len() - 1 {
      // This is the profiling data
      println!("Profiling info: {:?}", item);
    } else {
      // These are regular result documents
      println!("Result: {:?}", item);
    }
  }
  ```

  ```dart Dart theme={null}
  final results = await ditto.store.execute(
    "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
  );

  // The last item in results will contain the profiling information
  for (var i = 0; i < results.length; i++) {
    if (i == results.length - 1) {
      // This is the profiling data
      print("Profiling info: ${results[i]}");
    } else {
      // These are regular result documents
      print("Result: ${results[i]}");
    }
  }
  ```
</CodeGroup>

## PROFILE vs #profile Directive

The `PROFILE` statement is equivalent to using the `#profile` directive:

```sql DQL theme={null}
-- 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](/dql/explain) - View query execution plans without running the query
* [Directives](/dql/directives) - Control query planner behavior
* [Access Paths](/dql/access-paths) - Understanding query execution strategies
* [Indexing](/dql/indexing) - Creating indices to improve query performance