Benchmarking Package#

Utilities to optimize and execute queries and workloads in a reproducible and transparent manner.

postbound.bench.PredefLogger#

Pre-defined loggers that can be used to track progress during workload execution.

alias of Literal[‘tqdm’]

postbound.bench.ErrorHandling#

How to handle errors during optimization or execution:

raise: Raise the exception immediately
log: Include the failed query in the resulting data frame, just like successful queries. The status column will indicate
the specific error and the failure reason column will contain the exception message.
ignore: Silently ignore the error and do not include the failed query in the resulting data frame

alias of Literal[‘raise’, ‘log’, ‘ignore’]

postbound.bench.ExecStatus#

Describes the result of a query execution:

ok: The query was executed successfully
timeout: The query was cancelled due to a timeout
optimization-error: The query could not be optimized by PostBOUND
execution-error: The query could not be executed by the database system

For errors, the actual reason is contained in the failure_reason column of the resulting data frame.

alias of Literal[‘ok’, ‘timeout’, ‘optimization-error’, ‘execution-error’]

class postbound.bench.ExecutionResult(query: SqlQuery, status: Literal['ok', 'timeout', 'optimization-error', 'execution-error'] = 'ok', query_result: Sequence[tuple] | None = None, optimization_time: float = nan, execution_time: float = nan)#

Captures all relevant components of a query optimization and execution result.

Parameters:

query (SqlQuery)
status (Literal['ok', 'timeout', 'optimization-error', 'execution-error'])
query_result (Sequence[tuple] | None)
optimization_time (float)
execution_time (float)

query: SqlQuery#: The query that was executed. If the query was optimized and transformed, these modifications are included.

status: Literal['ok', 'timeout', 'optimization-error', 'execution-error'] = 'ok'#: Whether the query was executed successfully or not.

query_result: Sequence[tuple] | None = None#: The result set of the query or None if the query failed.

optimization_time: float = nan#

The time in seconds it took to optimized the query by PostBOUND.

This does not account for optimization by the actual database system and depends heavily on the quality of the implementation of the optimization strategies.

For queries that were not optimized within PostBOUND, this value is NaN.

execution_time: float = nan#

The time in seconds it took to execute the (potentially optimized) query by the actual database system.

This execution time includes the entire end-to-end processing, i.e. starting with supplying the query to the database until the last byte of the result set was transferred back to PostBOUND. Therefore, this duration also includes the optimization time by the database system, as well as the entire time for data transfer.

A value of Inf indicates that the query did not complete successfully and was cancelled due to a timeout. NaN encodes a failure during optimization or execution. See status for more details.

static passed(query: SqlQuery, *, query_result: Sequence[tuple], execution_time: float, optimization_time: float = nan) → ExecutionResult#

Constructs an ExecutionResult for a successfully executed query.

The optimization time can be omitted if the query was not optimized in PostBOUND.

Parameters:

query (SqlQuery)
query_result (Sequence[tuple])
execution_time (float)
optimization_time (float)

Return type:

ExecutionResult

static execution_error(query: SqlQuery, *, optimization_time: float = nan) → ExecutionResult#

Constructs an ExecutionResult for a query that failed during execution.

Parameters:

query (SqlQuery)
optimization_time (float)

Return type:

ExecutionResult

class postbound.bench.QueryPreparation(*, projection: Literal['none', 'star', 'count_star', '*', 'count(*)'] = 'none', output: Literal['default', 'explain', 'analyze', 'explain_analyze'] = 'default', explain: bool | None = None, count_star: bool | None = None, analyze: bool | None = None, prewarm: bool = False, preparatory_statements: list[str] | None = None)#

This service handles transformations of input queries that are executed before running the query.

These transformations mostly ensure that all queries in a workload provide the same type of result even in face of input queries that are structured slightly differently. For example, the preparation service can transform all the queries to be executed as EXPLAIN or COUNT(*) queries. Furthermore, the preparation service can store SQL statements that have to be executed before running the query. For example, a statement that disables parallel execution could be supplied here.

Parameters:

projection (Literal["none", "star", "count_star", "*", "count(*)"], optional) – Modify the SELECT clause of all queries to provide a uniform projection. Allowed values are none (no modification), star or * for SELECT * and count_star or count(*) for SELECT COUNT(*). Defaults to none.
output (Literal["default", "explain", "analyze", "explain_analyze"], optional) – What kind of results to gather. default executes the queries as they are, explain transforms all queries to EXPLAIN queries, analyze or explain_analyze transforms all queries to EXPLAIN ANALYZE queries.
explain (bool, optional) –
Whether to force all queries to be executed as EXPLAIN queries, by default False

Deprecated since version 0.21.0: This option is deprecated in favor of the more versatile output option.
count_star (bool, optional) –
Whether to force all queries to be executed as COUNT(*) queries, overwriting their default projection. Defaults to False

Deprecated since version 0.21.0: This option is deprecated in favor of the more versatile projection option.
analyze (bool, optional) –
Whether to force all queries to be executed as EXPLAIN ANALYZE queries. Setting this option implies explain, which therefore does not need to set manually. Defaults to False

Deprecated since version 0.21.0: This option is deprecated in favor of the more versatile output option.
prewarm (bool, optional) – For database systems that support prewarming, this inflates the buffer pool with pages from the prepared query.
preparatory_statements (Optional[list[str]], optional) – Statements that are executed as-is on the database connection before running the query, by default None

See also

db.PrewarmingSupport: Technical details on how prewarming is implemented in PostBOUND

prepare_query(query: SqlQuery, *, on: Database | None = None) → SqlQuery#

Applies the selected transformations to the given input query and executes the preparatory statements

Parameters:

query (SqlQuery) – The query to prepare
on (Optional[Database]) – The database to execute the preparatory statements on. If no database is given, only query transformations are applied.

Returns:

The prepared query

Return type:

SqlQuery

postbound.bench.ExecutionTarget = postbound.db._db.Database | postbound._pipelines.OptimizationPipeline | postbound._stages.OptimizationStage#

Specifies what to do with the workload queries:

providing a Database executes the queries as-is on the database
passing an OptimizationPipeline optimizes the queries using the pipeline before executing them on the target database of the pipeline
passing an OptimizationStage generates an appropriate optimization pipeline for the stage and then proceeds as above. Notice that in this mode, the target database is assumed to be the current database of the DatabasePool.

postbound.bench.execute_workload(queries: Iterable[SqlQuery] | Workload, on: Database | OptimizationPipeline | OptimizationStage, *, name: str = '<unnamed>', workload_repetitions: int = 1, per_query_repetitions: int = 1, shuffled: bool = False, query_preparation: QueryPreparation | dict[str, Any] | None = None, training_data: TrainingData | TrainingDataRepository | None = None, timeout: float | None = None, exec_callback: Callable[[ExecutionResult], None] | None = None, pre_exec_callback: Callable[[SqlQuery], None | dict] | None = None, post_exec_callback: Callable[[ExecutionResult], None | dict] | None = None, repetition_callback: Callable[[int], None] | None = None, progressive_output: str | Path | None = None, output_args: dict | None = None, logger: Callable[[str], None] | Literal['tqdm'] | None = None, error_action: Literal['raise', 'log', 'ignore'] = 'log') → DataFrame#

Simple benchmarking interface.

This function runs a query workload on a database system and measures the execution time of each query. All workload queries can be optimized through an OptimizationPipeline.

Parameters:

queries (Iterable[SqlQuery] | Workload) – The queries to be executed.
on (ExecutionTarget) – This is a catch-all parameter to specify the database system to execute the queries on, as well as the (optional) pipeline to optimize the queries. If a pipeline is provided, all queries are first passed through the pipeline before executing them on the pipeline’s target database. It is even possible to provide a single optimization stage, in which case the stage is first expanded into a full optimization pipeline.
name (str, optional) – A human-readable name that describes the current experiment. This is only used in log files, etc. to identify the the experiment.
workload_repetitions (int, optional) – The number of times the entire workload should be repeated. By default, the workload is only executed once.
per_query_repetitions (int, optional) – The number of times each query should be repeated within each workload repetition. The per-query repetitions happen sequentially one after another before transitioning to the next query. By default each query is only executed once.
shuffled (bool, optional) – Whether to randomize the execution order of each query within the workload. Shuffling is applied before each workload repetition. Per query repetitions are not influenced by this setting.
query_preparation (Optional[QueryPreparation | dict], optional) – Preparation steps that should be performed before running the query. The preparation result will be used in place of the original query for all repetitions. If a dictionary is passed, all keys are assumed to be valid parameters to the QueryPreparation constructor.
training_data (Optional[TrainingData | TrainingDataRepository], optional) – If any of the stages in the optimization pipeline requires training on data samples, this data has to be passed here. This only applies if the stages have not been trained already. Data-driven and workload-driven stages will be trained automatically without any explicit action needed by the user.
timeout (Optional[float], optional) – The maximum time in seconds that the query is allowed to run. If the query exceeds this time, the execution is cancelled and the execution time is set to Inf. If this parameter is omitted, no timeout is enforced. Notice that timeouts require the database to implement TimeoutSupport.
exec_callback (Optional[Callable[[ExecutionResult], None]], optional) –
An optional callback that is executed after each query execution.

Deprecated since version v0.21.1: This callback is deprecated in favor of post_exec_callback. Both are functionally equivalent. This is really just a renaming to not cause confusion regarding the precise moment when the callback is executed.
pre_exec_callback (Optional[Callable[[SqlQuery], None | dict]], optional) –
An optional callback that is executed right before each query execution. This query includes all hinted optimization decisions (if any).

The callback can either return None, or an arbitrary (JSON-serializable) dictionary. If it returns a dictionary, its keys are included as additional columns in the resulting data frame. The following rules must be followed:
1. If the callback returns a dictionary, it must always do so.
2. The callback must always return a dictionary of the same shape (i.e. same keys). If some keys should sometimes not contain values for whatever reason, appropriate placeholder/default values must be used. Selectively omitting a key (or an entire dictionary) results in an error.
3. All values in the dictionary must be JSON-serializable
post_exec_callback (Optional[Callable[[ExecutionResult], None | dict]], optional) –
An optional callback that is executed right after each query execution.

The callback can either return None, or an arbitrary (JSON-serializable) dictionary. If it returns a dictionary, its keys are included as additional columns in the resulting data frame. The following rules must be followed:
1. If the callback returns a dictionary, it must always do so.
2. The callback must always return a dictionary of the same shape (i.e. same keys). If some keys should sometimes not contain values for whatever reason, appropriate placeholder/default values must be used. Selectively omitting a key (or an entire dictionary) results in an error.
3. All values in the dictionary must be JSON-serializable
progressive_output (Optional[str | Path], optional) – If provided, results will be written to this file as soon as they are obtained. If the file already exists, it will be appended. Supported file formats are CSV, JSON, Parquet, and HDF. When writing to HDF, the output key must be specified in the output_args.
output_args (Optional[dict], optional) – Additional arguments to pass to the Pandas writer function for the progressive output file.
logger (Optional[Callable[[str], None] | PredefLogger], optional) –
Configures how progress should be logged. Depending on the specific argument, a number of different strategies are available:
- passing None (the default) disables logging
- passing a callable invokes the function before every query execution. It receives information about the current execution as argument
- referencing a pre-defined logger invokes. Currently, only tqdm is supported. It uses the corresponding library to print a progress bar
exec_callback – A post-process action that should be executed after each repetition of the query has been completed.
repetition_callback (Optional[Callable[[int], None]], optional) – An optional post-process action that is executed after each workload repetition. The current repetition number is provided as the only argument. Repetitions start at 1.
error_action (ErrorHandling, optional) – Configures how errors during optimization or execution are handled. By default, failing queries are still contained in the result data frame, but some columns might not contain meaningful values. Check the status column of the data frame to see what happened.

Returns:

The execution results for the input workload. The data frame will be structured as follows:

the data frame will contain one row per query repetition
exec_index contains an absolute index indicating when the query was executed
timestamp is the time when the query execution started
label is an identifier of the current query, usually inferred from the Workload object
workload_repetition indicates the current workload repetition
query_repetition indicates the current per-query repetition (in contrast to repetitions of the entire workload)
query contains the input query being executed. If the query was optimized or prepared, these modifications are not included here
status indicates whether the query was executed successfully, or whether an error occurred. Possible values are “ok”, “timeout”, “optimization-error”, and “execution-error”
result_set is the actual result of the query. Scalar results are represented as-is. In case of an error this will be None
exec_time contains the time it took to execute the query (in seconds). This includes the entire time from sending the query to the database until the last byte of the result set has been transferred back to PostBOUND. In case of an error this will be NaN and for timeouts this will be timeout itself.
failure_reason contains a description of the error that occurred during optimization or execution
db_config describes the database (and its state) on which the query was executed. The state is obtained just before query execution started and after the optimization and query preparation steps have been applied
query_preparation contains the settings that were used to prepare the query after optimization but before execution
optimization_time contains the time it took to optimize the query using PostBOUND (in seconds). If the query was not optimized, this will be NaN
optimization_pipeline contains the optimization pipeline that was used to optimize the query. If the query was not optimized, this will be None
optimized_query contains the optimized query that was actually executed on the database. If the query was not optimized, this will be None
additional columns added by the pre-exec or post-exec callbacks

Return type:

pd.DataFrame

Notes

If the database system does provide accurate timing information through the StopwatchSupport interface, these measurements will be preferred over the wall-clock timing that is obtained in the benchmarking process.

postbound.bench.prepare_export(results_df: DataFrame) → DataFrame#

Modifies a benchmark result dataframe such that it can be written to CSV files without problems.

This mostly involves converting Python objects to JSON counterparts that allow a reconstruction of equivalent data.

More specifically, the function handles two main aspects:

making sure that the query result can be written to CSV, and
making sure that the description of the optimization pipeline can be written to CSV.

In both cases, the column values will be transformed to JSON-objects if necessary.

Parameters:: results_df (pd.DataFrame) – The result dataframe created by one of the benchmark functions
Returns:: The prepared dataframe
Return type:: pd.DataFrame

Benchmarking Package

Contents

Benchmarking Package#