Sign inTry Free

SHOW PROFILE

On this page

Returns profiling information for queries and pipelines run in PROFILE mode. Use SHOW PROFILE after running the PROFILE command (or when auto profiling is enabled) to inspect resource‑usage metrics for a specific query plan, query execution, or pipeline. By default, SHOW PROFILE displays the execution profile as a tree with resource-usage metrics appended to each operator.

Syntax

SHOW PROFILE [JSON | UI]
  [PLAN <plan_id>]
  [PROCESS <process_id>]
  [PIPELINE <pipeline_name>]
  [ON NODE <node_id>]
  [INTO OUTFILE '<file_name>'];

Arguments

JSON

Returns profile information in JSON format for programmatic processing or external visualization. SHOW PROFILE JSON includes detailed operator statistics and compilation-time statistics such as compile_time_stats. It also includes partition-level details for skew-related metrics, even when there is no skew. For the complete JSON structure and examples, refer to Profile JSON Fields.

UI

Returns a URL that opens a visual representation of the profile in SingleStore Visual Explain.

PLAN plan_id

Returns profile information for a compiled query plan that has been executed in PROFILE mode and exists in the plancache. For details about plan IDs, refer to SHOW PLAN.

PROCESS process_id

Returns profile information for a specific query execution identified by its process ID. Returns the active profile if the query is currently running in PROFILE mode, otherwise returns the most recent completed profile for that process. Refer to SHOW PROCESSLIST or CONNECTION_ID() to obtain process IDs.

Note: To profile a hung or long-running query, run the PROFILE statement in one connection and SHOW PROFILE PROCESS in another connection while the query is running. This allows you to monitor the progress and resource usage of queries with long execution times.

PIPELINE pipeline_name

Returns profile information for a pipeline that has been run in PROFILE mode.

ON NODE node_id

Runs the command on the specified node. Use this option only in combination with PLAN, PROCESS, and PIPELINE.

INTO OUTFILE 'file_name'

Writes the profile output to a file. INTO OUTFILE cannot be used if the statement is forwarded to a different node.

Remarks

  • Refer to the PROFILE command for detailed information about enabling profiling, auto profiling configuration, metric definitions, output formats, and examples.

  • SHOW PROFILE displays existing profiling data. It does not execute or re‑profile a query or pipeline by itself. Running SHOW PROFILE for a plan, process, or pipeline that has never been profiled results in an error indicating that profile statistics do not exist.

  • SHOW PROFILE [JSON | UI] without PLAN, PROCESS, or PIPELINE returns the profile for the most recent statement that was run in PROFILE mode on that connection.

Examples

  • View the profile for the most recent query execution:

    PROFILE SELECT * FROM employees WHERE department = 'Engineering';
    SHOW PROFILE;
    

    +-------------------------------------+------------------------------------------+
    |PROFILE                                                                         |
    |Top limit:[@@SESSION.`sql_select_limit`] actual_rows: 4 exec_time: 0ms          |
    |Gather partitions:all alias:remote_0 parallelism_level:segment exec_time: 0ms   |
    |start_time: 00:00:00.000 end_time: 00:00:00.086 network_traffic: 0.101000 KB    |
    |network_time: 0ms actual_rows: 3                                                |
    |Project [employees.id, employees.name, employees.department] actual_rows: 3     |
    |exec_time: 0ms start_time: 00:00:00.086 network_traffic: 0.101000 KB            |
    |network_time: 0ms                                                               |
    |Top limit:[?] actual_rows: 3 exec_time: 0ms                                     |
    |ColumnStoreFilter [employees.department = ?] actual_rows: 3 exec_time: 1ms      |
    |start_time: 00:00:00.086 total_rows_in: 3 average_filters_per_row: 1.000000     |
    |average_index_filters_per_row: 0.000000 average_bloom_filters_per_row: 0.000000 |
    |ColumnStoreScan profiling_demo.employees, SORT KEY __UNORDERED ()               |
    |table_type:sharded_columnstore actual_rows: 3 exec_time: 64ms start_time:       |
    |00:00:00.021 memory_usage: 393.216003 KB number_of_blocks_tested_for_block_elim:|
    |0 number_of_blocks_eliminated_for_block_elim: 0 segments_scanned: 2             |
    |segments_skipped: 2 segments_fully_contained: 0 segments_in_blob_cache: 0       |
    |Compile Total Time: 25ms                                                        |
    +-----------------------------------+--------------------------------------------+

    Note: To get more accurate resource usage metrics, run the PROFILE statement twice followed by SHOW PROFILE. The first execution includes compilation time, while the second execution provides more accurate runtime statistics.

  • View profile information in JSON format for a specific process:

    SHOW PROFILE JSON PROCESS 4534;

  • View profile information for a specific plan:

    SHOW PROFILE PLAN 98765;

  • View profile information in JSON format for a specific plan:

    SHOW PROFILE JSON PLAN 4;

  • View profile information for a pipeline:

    SHOW PROFILE JSON PIPELINE my_data_pipeline;

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In