Optimizing a SQL database or query can be a daunting task, and in this day and age when data is considered the new gold or oil, that task is more important than ever before. With the enormous amount of complex data companies are gathering each and every day in order to get a leg up in the war for our increasingly short attention span, an inefficient database or a poorly crafted query could easily increase an application’s load-time, which can spell the end for a company.
War is ninety percent information.
-Napoleon Bonaparte, French Military and Political Leader
SeeQR was developed to help solve that exact problem — optimizing SQL databases and queries.
For those who are unfamiliar, SeeQR is an open source database analytic tool to compare the efficiency of different schemas and queries on a granular level, so that developers and architects can make better informed architectural decisions regarding SQL databases at various scales. Providing an abundance of information and metrics, from a database’s size and details of its tables to a query’s run time and plan of execution, this powerful dev tool ensures that developers have all the information they need to make an informed decision regarding their databases and queries.
Getting Started
When the application first launches, it connects to the local instance of PostgreSQL using the role ‘postgres’, so all databases that ‘postgres’ has access to will be available in the application. Besides using the existing databases, users can also create new databases by importing ‘.sql’ or ‘.tar’ files, or by copying an existing database, with or without its original data.
‘DATABASES’ View
In the ‘DATABASES’ view, users can select a table from a list of all the tables in the schema of the currently selected database. Information about the selected table will then display, and the name and size of the selected database will also be displayed near the top of the page. Users can also generate large amounts of foreign-key compliant dummy data for the selected table in the current database.
‘QUERIES’ View
In the ‘QUERIES’ view, users can select the database to use in the ‘Database’ dropdown above the main panel, and input a SQL query into the main panel. Also, SeeQR provides the options of executing a labelled or unlabelled query; labelled queries will be saved in the sidebar so that they can be referenced at a later time in the current session. To label a query, simply provide a label in the ‘Label’ field above the main panel to identify the query in later comparisons against other queries.
Once executed, the query’s output will be displayed. In addition, for eligible* queries, users will be able to view the queries’ planning time, execution time, total run time, and plan of execution. They can toggle between the executed query’s ‘RESULTS’ and ‘EXECUTION PLAN’ to see the returned results or the query’s plan of execution.
Within the ‘EXECUTION PLAN’, users will be able to see a tree of the executed query’s execution plan. Users can also adjust the thresholds of ‘Percentage of Total Duration’ and the ‘Planner Rows Accuracy’ that are used to highlight certain nodes in the tree, allowing them to easily identify potential areas of improvement. Clicking on a node card will display additional details regarding that action.
Comparing Queries
SeeQR also allows developers to compare the runtime of executed queries side-by-side in a visually appealing graph. Simply check or uncheck the box next to each saved query to add or remove the query from the graph. Graph will be organized along the x-axis by label, and colored by schema. In addition to the visualized performance comparison of the selected queries, a table will display information about each selected query, including its total run time and performance relative to other queries with the same label, with the most performant query highlighted.
Recap
SeeQR is a wonderful efficiency tool that can help guide informed decisions on optimizing SQL databases and queries! You can test it out for yourself by downloading the latest build at the official SeeQR website. If you like what you see, support us by starring the app’s GitHub repo. If you’d like to dive even deeper into PostgreSQL optimization, read this in-depth guide about PostgreSQL optimization through indexing. Cheers!
*Eligible queries include any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement
Core Contributors: Casey Escovedo, Casey Walker, Catherine Chiu, Chris Akinrinade, Cindy Chau, Claudio Santos, Faraz Akhtar, Frank Norton, James Kolotouros, Jennifer Courtner, Justin Dury-Agri, Katie Klochan, Mercer Stronck, Muhammad Trad, Richard Lam, Sam Frakes, Serena Kuo
