Profile Performance of Lookup Operations

Analyze query performance when using the explain:asTable() with defineTable() and match() for data enrichment

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2[(Function)] 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result
logscale
| defineTable(query={sort(name, order=ascending)}, include=[userid, name, role], name="users")
| match(users, field=["user_id"], column=userid)
| explain:asTable()

Introduction

The explain:asTable() function can be used to analyze the performance of queries involving table definitions and lookups, 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 creates a lookup table and performs data enrichment using match().

Example incoming data might look like this:

@timestampuser_idactionresource
2025-11-05T10:00:00ZU123loginapp1
2025-11-05T10:00:01ZU456logoutapp2
2025-11-05T10:00:02ZU789accessapp1
2025-11-05T10:00:03ZU123logoutapp2

And the user reference data (used in the lookup table) might look like this:

@timestampuseridnamerole
2025-11-05T10:00:00ZU123John Smithadmin
2025-11-05T10:00:00ZU456Jane Doeuser
2025-11-05T10:00:00ZU789Bob Wilsonuser

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2[(Function)] 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | defineTable(query={sort(name, order=ascending)}, include=[userid, name, role], name="users")

    Creates a lookup table named users containing user reference data, sorted in ascending order by the name field. The table includes only the fields specified in the include parameter: userid, name, and role.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2[(Function)] 3[(Function)] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | match(users, field=["user_id"], column=userid)

    Performs a lookup operation that enriches the main event stream with data from the users lookup table. The lookup matches the user_id field from the main events with the userid column from the lookup table, adding the corresponding name and role fields to matching events.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2[(Function)] 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()

    Generates a performance profile of the entire query execution, including the table definition and lookup operations. Uses default settings to show all execution steps, including prefilters.

  5. Event Result set.

Summary and Results

The query is used to analyze the performance characteristics of table creation and lookup operations in LogScale.

This query is useful to understand the performance impact of subqueries as well as lookup table operations.

Sample output from the incoming example data:

stepIDstepadditionalDataeventstimeMs
<no value>table: users<no value><no value>200
"","prefilters","bytes searched: 49,834    
bytes skipped: 0    
skip rate: 0","",""    
1match(users, field=["user_id"], column=userid)<no value>14

Note that the output shows the execution time for each major operation: table definition, sorting, and matching. The prefilter steps indicate how many events were initially considered before the main operations began.

The match step shows the total number of events that were successfully enriched with data from the lookup table, while the table definition steps show the number of events processed for creating the lookup table.