PGSQLTune: Postgresql SQL Tuning Advisor

April 3, 2026

PGSQLTune is a PostgreSQL extension designed to analyze SQL queries and provide practical recommendations for improvement. Currently, it offers basic suggestions. Our product is under development and will offer many more suggestions in the future.

Click here to download the PGSQLTune plugin .

Setup Instructions

First, extract the downloaded file to a directory:

$ unzip pgsqltune.zip

Then, perform the binary package installation from the Linux terminal as shown below. Since the pg_config tool is required, make sure the directory containing pg_config is included in your PATH variable. The example below adds the binary directory for PostgreSQL version 17 to the PATH. You should adjust this path according to the PostgreSQL version you have installed.

$ cd /path/to/pgsqltune 
$ export PATH=$PATH:/usr/pgsql-17/bin/ 
$ make 
$ make USE_PGXS=1 install

After the installation, connect to your database using psql and create the extension:

postgres=# CREATE EXTENSION pgsqltune;

Usage

To use the extension, call the following function with your SQL query:

SELECT pg_sqltune($$ <sql_text> $$);

Example

postgres=# SELECT pg_sqltune($$SELECT * FROM employees WHERE age > 30 AND department = 'Dept 2'$$);

Output:

postgres=# SELECT pg_sqltune($$SELECT * FROM employees WHERE age > 30 AND department = 'Dept 2'$$); 
                                                                         pg_sqltune                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 FUNCTIONAL EXPRESSION: Filter "((employees.age > '30'::numeric) AND ((employees.department)::text = 'Dept 2'::text))" contains function calls.             +
 Consider creating a functional index if this filter is used frequently.                                                                                    +
 Example: CREATE INDEX idx_employees_expr ON employees USING btree((((employees.age > '30'::numeric) AND ((employees.department)::text = 'Dept 2'::text))));+
                                                                                                                                                            +
 FILTER CONDITION: Table employees has filter on column age (((employees.age > '30'::numeric) AND ((employees.department)::text = 'Dept 2'::text)))         +
 Recommendation: CREATE INDEX idx_employees_age ON employees(age);                                                                                          +
                                                                                                                                                            +
 FILTER CONDITION: Table employees has filter on column department (((employees.age > '30'::numeric) AND ((employees.department)::text = 'Dept 2'::text)))  +
 Recommendation: CREATE INDEX idx_employees_department ON employees(department);                                                                            +
                                                                                                                                                            +
 MULTI-COLUMN FILTER: Consider composite index for columns age,department                                                                                   +
 Recommendation: CREATE INDEX idx_employees_comp ON employees(age,department);                                                                              +
 Column order suggestion: Place high-selectivity columns first                                                                                              +
                                                                                                                                                            +
                                                                                                                                                            +
 GENERAL RECOMMENDATIONS:                                                                                                                                   +
 - Run VACUUM ANALYZE on tables to update statistics                                                                                                        +
 - Consider increasing work_mem for complex sorts/hashes                                                                                                    +
 - Review shared_buffers and maintenance_work_mem settings                                                                                                  +
 - For large tables, consider partitioning strategies                                                                                                       +
 - Use prepared statements for frequently run queries                                                                                                       +
 - Check for unused indexes that could be dropped                                                                                                           +
                                                                                                                                                            +
 QUERY ID: 5953ded824e82e73c4fe69bd825d1a21                                                                                                                 +
 HASH: 1092610392                                                                                                                                           +
                                                                                                                                                            +
                                                                                                                                                            +
 CURRENT SETTINGS:                                                                                                                                          +
 work_mem = 4MB\nshared_buffers = 128MB\nmaintenance_work_mem = 64MB\nrandom_page_cost = 4\neffective_cache_size = 4GB\n
(1 row)

Share

PGSQLTune: Postgresql SQL Tuning Advisor

Leave a Reply Cancel reply

You may also like

Oracle ACE Pro

Oracle Certified Professional, Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administrator

Oracle Certified Professional, MySQL 8.0 Database Administrator

Oracle Cloud Infrastructure 2025 Certified Foundations Associate

Oracle Database Administration 2019 Certified Professional

Oracle Database 11g Administrator Certified Professional

Oracle Database 11g Administrator Certified Associate

Oracle Exadata Database Machine 2017 Solution Engineer Specialist

IBM Certified System Administrator AIX 7

PostgreSQL Essentials v13 Certification