Increasing performance by using proper query structure
Earlier in our previous article Improve efficiency of your SELECT queries we discussed ways to profile and optimize the performance of SELECT queries. However, to write complex yet efficient SQL queries, there is a thing to remember about.
The RDBMS query interpreter is pretty good at applying optimizations, but it does not know exactly what you want. Sometimes, to help it, you have to be extra careful with what structure of query you are using. Let us consider different database schema for this example:
CREATE TABLE pizza_recipe (
id serial PRIMARY KEY,
name text UNIQUE NOT NULL
);
CREATE TABLE pizza_crust (
id serial PRIMARY KEY,
name text UNIQUE NOT NULL
);
CREATE TABLE pizza_restaurant (
id serial PRIMARY KEY,
name text UNIQUE NOT NULL
);
CREATE TABLE pizza_takeout (
id serial PRIMARY KEY,
pizza_recipe_id integer references pizza_recipe(id) NOT NULL,
pizza_crust_id integer references pizza_crust(id) NOT NULL,
pizza_restaurant_id integer references pizza_restaurant(id) NOT NULL,
order_time timestamp DEFAULT current_timestamp,
address text NOT NULL
);
CREATE INDEX pizza_recipe_id__pizza_takeout__idx ON pizza_takeout (pizza_recipe_id);
CREATE INDEX pizza_crust_id__pizza_takeout__idx ON pizza_takeout (pizza_crust_id);
CREATE INDEX pizza_restaurant_id__pizza_takeout__idx ON pizza_takeout (pizza_restaurant_id);
INSERT INTO pizza_recipe (name)
VALUES
('Pepperoni'),
('Texas'),
('Margherita');
INSERT INTO pizza_crust (name)
VALUES
('Ordinary'),
('Cheeze'),
('Sausage');
INSERT INTO pizza_restaurant (name)
VALUES
('Restaurant 1'),
('Restaurant 2'),
('Restaurant 3');
INSERT INTO pizza_takeout (pizza_recipe_id, pizza_restaurant_id, pizza_crust_id, address)
VALUES
(1, 1, 1, ''),
(1, 1, 1, ''),
(1, 1, 2, ''),
(1, 1, 3, ''),
(1, 2, 1, ''),
(1, 2, 1, ''),
(1, 2, 2, ''),
(1, 2, 3, ''),
(1, 3, 1, ''),
(1, 3, 1, ''),
(1, 3, 2, ''),
(1, 3, 3, ''),
(2, 3, 1, ''),
(2, 3, 1, ''),
(2, 3, 2, ''),
(2, 3, 3, ''),
(3, 3, 1, ''),
(3, 3, 1, ''),
(3, 3, 2, ''),
(3, 3, 3, '');Here we have a pizza restaurant network. Its schema consists of a kind of pizza (pizza_recipe), crust (pizza_crust), restaurant (pizza_restaurant), and order for pizza delivery (pizza_takeout). Suppose we need to implement the report showing a count of pizza orders in the following restaurants, with particular kind and particular crust. It should look like this:
There are multiple ways to form a data structure that will look like this on the frontend, but I find the following approach the most suitable. Here is the API response schema:
{
"restaurant_list": [
{
"restaurant_id": 1,
"restaurant_name": "restaurant 1",
"recipe_list": [
{
"recipe_id": 1,
"recipe_name": "Pepperoni",
"crust_list": [
{
"crust_id": 1,
"recipe_id": 1,
"restaurant_id": 1,
"crust_name": "Ordinary crust",
"count": 20
},
{
"crust_id": 2,
"recipe_id": 1,
"restaurant_id": 1,
"crust_name": "Cheeze crust",
"count": 10
},
{
"crust_id": 3,
"recipe_id": 1,
"restaurant_id": 1,
"crust_name": "Sausage crust",
"count": 5
}
]
},
...
]
},
...
]
}One of its advantages is that we are able to get such a response schema by using only database queries and its connection engine in your programming language. For example, Python gets JSON objects from the database and transforms them into a JSON response pretty efficiently.
However, there are two ways to implement it. The first one is more intuitive and straightforward. It includes selecting top-level entities and getting its child data via a hierarchy of subqueries. It may be implemented like this:
And its EXPLAIN looks like this:
The lower rows are less important, what really interests us is the overall cost at line 3. The worst-case cost looks like this query could become a real bottleneck on large data samples. Let us think about why is it so slow.
The problem is that we have nested subqueries in this query. This means that on every combination of restaurant, pizza, and crust we will query database for pizza_takeout records. Let us try to write a query that will have a more predictable cost.
And here is its output:
As you can see, the query build plan is shorter and the cost is dramatically smaller. Why is that? Looking closer into the second query, you can see that the main FROM is built around the selection of pizza_takeout records. This gives us the following advantages:
The query has the single reliable “backbone“ selection - pizza_takeout_agg, all the other data is joined to it;
This selection has the largest amount of records (compare the possible amount of pizza recipes to pizza orders), so it is most logical to get this data in the least amount of database queries as possible;
If for some reason, you will be having a separate table pizza_restaurant_order for storing data about people who visit your restaurant, and you will want to have unified statistics between orders and takeouts, you may apply UNION inside the pizza_takeout_agg, and you will have this report updated without major structure change;
This pizza example is designed to show that in SQL there are multiple ways to do something, and you want to be careful about choosing the most obvious one. Moreover, the example is pretty typical, so you may encounter something very similar if you are involved into some sort of Business Intelligence domain.
Conclusion
SQL SELECT queries are all about formulating a proper way to extract and arrange data. So, when writing a query, you should keep the data structure in mind, thinking about how different entities are connected and how expensive would it be to arrange them in a certain way. If it feels that selection is slow or might be slow on larger datasets - it is recommended to generate large data samples and profile query with EXPLAIN ANALYZE statements.