Profile Performance of Join Operations

Analyze query performance of join operations with filtering using the explain:asTable() function with join()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[(Function)] 2{{Aggregate}} 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result
logscale
join(field=x, query={x=42})
| count()
| explain:asTable(showPrefilters=false)

Introduction

The explain:asTable() function can be used to analyze the performance of join operations, providing detailed execution metrics for each step in the query plan.

In this example, the explain:asTable() function is used to profile a query that performs a join operation with a filter condition.

Example incoming data might look like this:

@timestampxvalue
2025-11-05T10:00:00Z42100
2025-11-05T10:00:01Z42200
2025-11-05T10:00:02Z41300
2025-11-05T10:00:03Z42400
2025-11-05T10:00:04Z43500

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[(Function)] 2{{Aggregate}} 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    join(field=x, query={x=42})

    Performs a join operation on the field x, matching only events where x equals 42. The field parameter specifies which field to join on, while the query parameter filters the events to include in the join.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[(Function)] 2{{Aggregate}} 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | count()

    Counts the number of events resulting from the join operation.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[(Function)] 2{{Aggregate}} 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | explain:asTable(showPrefilters=false)

    Generates a performance profile of the query execution, with the showPrefilters parameter set to false to exclude prefilter statistics from the output.

  5. Event Result set.

Summary and Results

The query is used to analyze the performance characteristics of a filtered join operation followed by an aggregation.

This query is useful, for example, to understand the performance impact of join operations and their associated filters, helping to optimize queries that require correlating events based on field values.

Sample output from the incoming example data:

stepIDsteptimeMsevents 
<no value>join subquery at stepID=210<no value> 
1x = *51 
2join(field=x, query={x=42})52 
"3","count()","3",1"    

Note that the output shows three distinct steps: the join operation itself, the filter condition, and the final count. The events column shows how many events are processed at each step, with the join operation processing all events, the filter reducing to matching events, and the count producing a single result.

The execution time (timeMs field) for each step helps identify which parts of the query are most resource-intensive.