@Rocky Is there a way to get a json from EXPLAIN ANALYZE <statement>?

Yes, you can obtain the query profile in JSON format by changing the StarRocks frontend (FE) configuration. By default,

EXPLAIN ANALYZE

returns a text-based profile. To switch this to JSON, you need to modify the cluster-wide FE configuration

profile_info_format

. Method 1: Change Global FE Configuration You can set the profile output format to JSON using the following command (requires administrative privileges):

sql
ADMIN SET FRONTEND CONFIG ("profile_info_format" = "json");

Once this is set, running

EXPLAIN ANALYZE <statement>

will return the profile in a structured JSON format instead of the default hierarchical text. * Pros: Direct JSON output from the SQL command. * Cons: This is a global setting that affects all users and tools relying on the text profile output. Method 2: Query Detail HTTP API If you prefer not to change global settings, you can retrieve query details (including the profile) in JSON format via the StarRocks FE HTTP API. This is often better for programmatic analysis. Endpoint:

GET /api/query_detail

The response will include a

profile

field. While the field itself contains the profile as a string, the API response is a machine-parsable JSON object containing various metadata (CPU cost, memory usage, scan rows, etc.). Important Considerations * Version Support: The

profile_info_format

configuration was introduced in v2.5. The

EXPLAIN ANALYZE

command was introduced in v3.1. * Pipeline Profile Level: To ensure the JSON contains all detailed metrics, you may want to set the session variable

SET pipeline_profile_level = 2;

before running your analysis, though the default (level 1) is usually sufficient for most performance tuning. * Visualization: If your goal is to visualize the plan rather than parse it programmatically, the StarRocks Web UI (

http://<fe_ip>:<fe_http_port>/query

) provides a built-in visualizer for queries that have profiling enabled (

SET enable_profile = true;

). References • administration/management/FE_configuration.md • sql-reference/System_variable.md

@Rocky setting ADMIN SET FRONTEND CONFIG ("profile_info_format" = "json"); and running EXPLAIN_ANALYZE doesn't return a json

I apologize for the confusion. While the StarRocks FE configuration

profile_info_format

exists, it currently does not change the output of the

EXPLAIN ANALYZE

or

ANALYZE PROFILE

commands in your SQL client (CLI). In StarRocks,

EXPLAIN ANALYZE

and its related SQL commands are specifically designed to return a text-based, human-readable tree for use in terminal environments. The

profile_info_format

setting primarily affects how profiles are stored internally or served via certain backend actions (like the HTTP API). To get a structured JSON profile, you should use the following alternative methods: 1. FE HTTP API (Recommended) This is the standard way to retrieve a machine-readable JSON profile. * Endpoint:

GET http://<FE_IP>:<FE_HTTP_PORT>/api/query_detail?query_id=<QUERY_ID>

* Authentication: Use your database username and password. * Note: You must first run your query with profiling enabled (

SET enable_profile = true;

) to generate a

query_id

. You can find the ID by running

SELECT last_query_id();

immediately after your statement. 2. Query Profile API (v3.x+) You can get the specific execution profile (the same data

EXPLAIN ANALYZE

shows) in a more structured way via: * Endpoint:

GET http://<FE_IP>:<FE_HTTP_PORT>/api/query_profile?query_id=<QUERY_ID>

3. StarRocks Web UI If you need to visualize or export the data without manual API calls: 1. Navigate to

http://<FE_IP>:<FE_HTTP_PORT>/query

. 2. Find your query in the list. 3. Click on the Profile link. 4. The Web UI internally uses the JSON format to build the visualization, and in many browser versions, you can see the raw JSON response in the "Network" tab of your developer tools. Summary of SQL limitations *

EXPLAIN ANALYZE

: Always returns text (optimized for CLI). *

ANALYZE PROFILE FROM '<id>'

: Always returns text. *

get_query_profile('<id>')

: This SQL function returns the profile as a string (text), not a JSON object. If you are building a tool to parse profiles, the HTTP API is the only reliable way to get structured JSON data. For JSON output. References • administration/http_interface/query_detail.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN_ANALYZE.md • best_practices/query_tuning/query_profile_overview.md References • administration/management/FE_configuration.md

@Rocky Is there a way to get the profile of the query I am running through the mysql protocol to ensure I know what to look for?

Unfortunately,

EXPLAIN ANALYZE

in StarRocks does not currently support JSON output directly via the MySQL protocol (CLI). It is hardcoded to return a human-readable text tree. Even if you set

ADMIN SET FRONTEND CONFIG ("profile_info_format" = "json");

, this setting primarily affects how profiles are stored internally or served via the *HTTP API*; it does not change the output format of the

EXPLAIN ANALYZE

or

ANALYZE PROFILE

SQL commands. To get a JSON profile, you have two main options: 1. Use the FE HTTP API (Best for JSON) This is the only way to get a fully structured, machine-parsable JSON profile. * Step 1: Enable profiling in your session:

sql
    SET enable_profile = true;
    <your_query_here>;
    SELECT last_query_id(); -- Copy the returned ID

* Step 2: Call the HTTP endpoint (replace placeholders):

bash
    curl -u user:password http://<FE_IP>:<FE_HTTP_PORT>/api/query_profile?query_id=<QUERY_ID>

_If

profile_info_format

is set to

json

, this endpoint will return the structured profile._ 2. SQL Function:

get_query_profile

You can retrieve the profile as a single string using the MySQL protocol, which is useful if you are using a script to extract specific metrics.

sql
SET enable_profile = true;
<your_query_here>;
SELECT get_query_profile(last_query_id());

_Note: In most versions, even with the JSON config enabled, this function still returns a text-based string. However, you can use SQL functions like

regexp_extract

on this string to "look for" specific values programmatically._ Why the JSON config didn't work for you The

profile_info_format

configuration is used by the StarRocks Web UI and the HTTP API. When you run

EXPLAIN ANALYZE

in a MySQL client, the Frontend (FE) reformats the data into the standard text-based "Explain String" to remain compatible with traditional MySQL CLI expectations. Summary of "What to look for" If you are trying to parse the profile manually to find bottlenecks, look for these keywords in the text output (or JSON): *

ScanTime

/

IOTime

: High values indicate slow disk/cloud storage access. *

PushdownPredicates

: Ensure your

WHERE

clauses are actually reaching the storage layer. *

NetworkTime

/

ShuffleTime

: High values suggest large data broadcasts or shuffles (consider optimizing Joins). *

PeakMemoryUsage

: Check if specific operators are hitting memory limits. Recommendation: For the best experience, use the StarRocks Web UI (

http://<FE_IP>:<FE_HTTP_PORT>/query

). It provides a visual representation of the JSON profile that is much easier to navigate than raw JSON. References • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/information_schema/session_variables.md • sql-reference/information_schema/verbose_session_variables.md • using_starrocks/Flat_json.md