0

Could anyone please point me to a benchmark comparing the time spent by the query optimiser relative to the overall query execution time (any relational DBMS will do, but PostgreSQL would be preferable)?

I'm curious about roughly how significant is the time spent on optimizing the query relative to the overall execution time.

In particular, whilst reading Bao: Learning to Steer Query Optimizers by Marcus et al. 2020 I was surprised by the fact that the DBMS' query optimizer was run 48 times for each query (required even at inference), whilst the model outperformed the default PostgreSQL query performance regardless.

3
  • 5
    You can check that yourself if you run explain (analyze) on your query. The result will show the planning time and the execution time. – a_horse_with_no_name Nov 4 '20 at 21:50
  • 2
    Except in the extremely rare edge cases, the optimisation phase typically completes in few milliseconds (not just in Postgres). You be the judge how that compares to the usual execution time of your queries. – mustaccio Nov 4 '20 at 23:25
  • So kind of related to what everyone else is saying, the answer is it depends, there's no single cutoff time value in most modern RDBMS, but typically it's within milliseconds. In Microsoft SQL Server (and probably similarly in PostgreSQL, Oracle PL/SQL, etc) a multitude of execution plans are evaluated and assigned scores based on their efficiency in pulling the data so that they can be compared and the best plan can be selected. – J.D. Nov 4 '20 at 23:49
1

There is no absolute number for the percentage of database time spent planning queries, it depends on your workload.

  • If the workload consists of analytical queries, planning time is usually insignificant in comparison to query execution time.

  • If the workload consists of simple OLTP queries, planning time can exceed query execution time.

I didn't read the article you quote, but the PostgreSQL optimizer is invoked once per SQL statement executed. Of course it considers several plans to find the best one.

For a simple benchmark, you can use the built-in PostgreSQL benchmark tool pgbench. While it is not a full-fledged workload simulator, it is surprisingly volatile. I'll use the default workload, which is a simple OLTP workload.

First, create a sample database with a scale factor appropriate for what you want to test (in this case, 10 million rows in the largest table):

pgbench -i -s 100 mydb

Then run the benchmark with the “simple query protocol” (one-shot queries that are planned each time they are executed):

pgbench -c 4 -T 60 mydb
[...]
tps = 3922.296560 (excluding connections establishing)

Repeat the benchmark using the extended query protocol with prepared statements, so that query plans are reused (this roughly corresponds to query execution time without planning):

pgbench -c 4 -M prepared -T 60 test
[...]
tps = 5022.185127 (excluding connections establishing)

So in this simple scenario, we got more than 25% more transactions done by excluding planning time. For more reliable values, run the test for more than 60 seconds.

Of course this number doesn't accurately measure query planning time versus query execution time, because there is still the latency of the round trip between client and server to consider. You could get better numbers by writing your own PL/pgSQL code that runs static or dynamic SQL statements in a tight loop. Since the plans of static SQL statements in PL/pgSQL statements are cached, their runtime will exclude query planning time.

You can also write your own benchmarks with pgbench; it allows you to write custom scripts and even offers a simple scripting language.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.