Home  /  Reference

EXPLAIN TIMESTAMP

EXPLAIN TIMESTAMP displays the timestamps used for a SELECT statement – valuable information to investigate query delays.

WARNING! EXPLAIN is not part of Materialize’s stable interface and is not subject to our backwards compatibility guarantee. The syntax and output of EXPLAIN may change arbitrarily in future versions of Materialize.

Syntax

EXPLAIN TIMESTAMP AS TEXT JSON FOR select_stmt

Output format

You can select between JSON and TEXT for the output format of EXPLAIN TIMESTAMP. Non-text output is more machine-readable and can be parsed by common graph visualization libraries, while formatted text is more human-readable.

Output type Description
TEXT Format the explanation output as UTF-8 text.
JSON Format the explanation output as a JSON object.

Details

The explanation is divided in two parts:

  1. Determinations for a timestamp
  2. Sources frontiers

Having a query timestamp outside the [read, write) frontier values of a source can explain the presence of delays. While in the middle, the space of processed but not yet compacted data, allows building and returning a correct result immediately.

Determinations for a timestamp

Queries in Materialize have a logical timestamp, known as query timestamp. It plays a critical role to return a correct result. Returning a correct result implies retrieving data with the same logical time from each source present in a query.

In this case, sources are objects providing data: materialized views, views, indexes, tables, or sources. Each will have a pair of logical timestamps frontiers, denoted as sources frontiers.

This section contains the following fields:

Field Meaning Example
query timestamp The query timestamp value 1673612424151 (2023-01-13 12:20:24.151)
oracle read The value of the timeline’s oracle timestamp, if used. 1673612424151 (2023-01-13 12:20:24.151)
largest not in advance of upper The largest timestamp not in advance of upper. 1673612424151 (2023-01-13 12:20:24.151)
since The maximum read frontier of all involved sources. [1673612423000 (2023-01-13 12:20:23.000)]
upper The minimum write frontier of all involved sources [1673612424152 (2023-01-13 12:20:24.152)]
can respond immediately Returns true when the query timestamp is greater or equal to since and lower than upper true
timeline The type of timeline the query’s timestamp belongs Some(EpochMilliseconds)

A timeline value of None means the query is known to be constant across all timestamps.

Sources frontiers

Every source has a beginning read frontier and an ending write frontier. They stand for a source’s limits to return a correct result immediately:

  • Read frontier: Indicates the minimum logical timestamp to return a correct result (advanced by compaction)
  • Write frontier: Indicates the maximum timestamp to build a correct result without waiting for unprocessed data.

Each source has its own output section consisting of the following fields:

Field Meaning Example
source Source’s identifiers source materialize.public.raw_users (u2014, storage)
read frontier Minimum logical timestamp. [1673612423000 (2023-01-13 12:20:23.000)]
write frontier Maximum logical timestamp. [1673612424152 (2023-01-13 12:20:24.152)]

Examples

EXPLAIN TIMESTAMP FOR SELECT * FROM users;

                                 Timestamp
---------------------------------------------------------------------------
                 query timestamp: 1673618185152 (2023-01-13 13:56:25.152) +
           oracle read timestamp: 1673618185152 (2023-01-13 13:56:25.152) +
 largest not in advance of upper: 1673618185152 (2023-01-13 13:56:25.152) +
                           upper:[1673618185153 (2023-01-13 13:56:25.153)]+
                           since:[1673618184000 (2023-01-13 13:56:24.000)]+
         can respond immediately: true                                    +
                        timeline: Some(EpochMilliseconds)                 +
                                                                          +
 source materialize.public.raw_users (u2014, storage):                    +
                   read frontier:[1673618184000 (2023-01-13 13:56:24.000)]+
                  write frontier:[1673618185153 (2023-01-13 13:56:25.153)]+

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schemas that all relations in the query are contained in.
Back to top ↑