Query Tuning
YugabyteDB provides a number of familiar performance tuning tools you can use to tune queries.
Find slow queries
Use the pg_stat_statements extension to get statistics on past queries. Using pg_stat_statements, you can investigate queries by userid and dbid, calls, rows, and min, max, mean, standard deviation and total time.
The pg_stat_statements extension module is installed by default, but must be enabled for a database before you can query the pg_stat_statements view.
CREATE EXTENSION if not exists pg_stat_statements;
To get the output of pg_stat_statements in JSON format, visit https://<yb-tserver-ip>:13000/statements in your web browser, where <yb-tserver-ip> is the IP address of any YB-TServer node in your cluster.
For more information, refer to Get query statistics using pg_stat_statements.
View plans with EXPLAIN
Like PostgreSQL, YugabyteDB provides the EXPLAIN statement to show the query execution plan generated by YSQL for a given SQL statement. Using EXPLAIN, you can discover where in the query plan the query is spending most of its time, and using this information, decide on the best approach for improving query performance. This could include strategies such as adding an index or changing index sort order.
For more information, refer to Analyze queries with EXPLAIN.
Advanced tools
Use the following tools to log slow-running queries and optimize queries using hint plans.
Log slow queries
You can set the --ysql_log_min_duration_statement flag to help track down slow queries. When configured, YugabyteDB logs the duration of each completed SQL statement that runs the specified duration (in milliseconds) or longer. (Setting the value to 0 prints all statement durations.)
$ ./bin/yb-tserver --ysql_log_min_duration_statement 1000
Example log output:
2021-05-26 21:13:49.701 EDT [73005] LOG: duration: 34.378 ms statement: SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(products)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
Results are written to the current postgres*log file. For information on the YB-TServer logs, refer to YB-TServer logs.
Note
Depending on the database and the work being performed, long-running queries don't necessarily need to be optimized.
Ensure that the threshold is high enough so that you don't flood the postgres*log log files.
For more information on flags for configuring the YB-TServer server, refer to YSQL Flags.
Use a hint plan
YugabyteDB uses the PostgreSQL pg_hint_plan extension to control query execution plans with hints.
pg_hint_plan makes it possible to influence the query planner using so-called "hints", which are C-style comments that use a special syntax.
Note
To usepg_hint_plan effectively, you need a thorough knowledge of how your application will be deployed. Hint plans also need to be revisited when the database grows or the deployment changes to ensure that the plan is not limiting performance rather than optimizing it.
For more information, refer to Optimizing YSQL queries using pg_hint_plan.
