PostgreSQL - SQL Optimization
Last Updated :
07 Oct, 2021
PostgreSQL is the most advanced general-purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop application or as a web in your browser. You can download the pgAdmin management tool from here.
We need optimization in SQL queries to get accurate and faster results in a more structured way. This will increase the performance and speedup of the process.
In this article we are going to discuss various SQL Optimization techniques in PostgreSQL. There are basically two types of optimization queries :
1. Inclusion
- IN clause : IN operator is mostly used with WHERE Clause to test for a given expression or a record it matches with a particular value from a set of values. It works like a multiple OR operator.
- ANY clause : It is used for comparison which returns Boolean TRUE value if the condition provided in the subquery is obtained for any values. If the condition is true for any of the values in that range then the output is TRUE.
- EXISTS clause : EXISTS operator mainly used in nested query (query inside a query). It is used to check the existence of the record provided in the subquery. It returns Boolean TRUE if one or more records match else FALSE when no rows match.
- INNER JOIN : Values matched in both tables are returned.
2. Exclusion
- NOT IN : It is just the negation of the IN clause which helps to execute the rows which don't match the set of values.
- ALL : It is also used for comparison which returns Boolean TRUE value if the condition provided in the subquery is obtained for all the values. If the condition is true for all the values in that range then the output is TRUE.
- NOT EXISTS : It is just the negation of EXISTS clause.
- LEFT JOIN AND IS NULL : It is used to find all the rows which have NULL entries after performing LEFT JOIN operation on two tables.
Let's implement the above SQL optimization techniques using a suitable example. Consider the sample tables shown below of an E-Commerce database.
| Purchase Information |
|---|
| Product ID | Mobile Brand | Price | Customer Name |
|---|
| 1 | OnePlus Nord 5G | 30000 | Rishabh |
| 2 | Samsung Galaxy M51 | 28000 | Srishti |
| 3 | iPhone 12 Pro | 128000 | Aman |
| 4 | Samsung Galaxy S20 | 55000 | Harsh |
| 5 | RealMe X50 Pro | 40000 | Manjari |
| 6 | Mi 10i 5G | 24000 | Satadru |
BASIC SQL QUERY :
1. Creating a Database
CREATE DATABASE database_name
2. Creating a Table
CREATE TABLE Table_name(
col_1 TYPE col_1_constraint,
col_2 TYPE col_2 constraint
.....
)
col: Column name
TYPE: Data type whether an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc
3. Inserting into a Table
INSERT INTO Table_name
VALUES(val_1, val_2, val_3, ..........)
val: Values in particular column
4. View The Table
SELECT * FROM Table_name
INCLUSION QUERIES :
1. IN CLAUSE :
SELECT col_name(s)
FROM Table1
WHERE value IN
(SELECT col_name(s) FROM Table2
WHERE condition)
col_name : Name of the columnSELECT col_name(s)
FROM Table_Name
WHERE col_name IN (val1,val2,val3,...)
col_name : Name of the column
val1 : Values in the column
- Retrieve all the purchase details who bought mobile phones and the price is more than thirty thousand.
IN CLAUSE- Retrieve all the purchase details who bought Samsung brand phones.
IN CLAUSE
2. ANY CLAUSE
SELECT col_name(s)
FROM Table_Name
WHERE col_name rln_opr ANY
(SELECT col_name
FROM Table_Name
WHERE condition)
rln_opr: Relational Operator like (>,>=,<,<=,!= or <>,=)
- Retrieve all those customers information from the Customer Information Table who purchased phones and whose price is more than thirty thousand.
ANY CLAUSE
3. EXISTS CLAUSE
SELECT col_name(s)
FROM Table_Name WHERE EXISTS
(SELECT col_name(s)
FROM Table_Name
WHERE condition)
- Retrieve all those customers who bought mobile phones and their details exist in the customer information table.
EXISTS CLAUSE
4. INNER JOIN
SELECT * FROM Table1
INNER JOIN Table2
ON Table1.column_match=Table2.column_match;
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
INNER JOIN OUTPUT
EXCLUSION QUERIES :
1. NOT IN
SELECT col_name(s)
FROM Table1
WHERE value NOT IN
(SELECT col_name(s) FROM Table2
WHERE condition)
SELECT col_name(s)
FROM Table_Name
WHERE col_name NOT IN (val1,val2,val3,...)
- Retrieve all the purchase details who bought mobile phones whose price is not more than thirty thousand.
NOT IN CLAUSE- Retrieve all the purchase details who didn't buy Samsung brand phones.
NOT IN CLAUSE
2. ALL
SELECT col_name(s)
FROM Table_Name
WHERE col_name rln_opr ALL
(SELECT col_name
FROM Table_Name
WHERE condition)
rln_opr: Relational Operator like (>,>=,<,<=,!= or <>,=)
- Retrieve the purchase information for those who purchased phones having prices not less than fifty thousand.
ALL OUTPUT- Retrieve all those purchase details of the mobile phones whose price is greater than and less than the average price of all the mobiles sold.
Average Price
Greater than ALL OUTPUT
Less than ALL Output
3. NOT EXISTS
SELECT col_name(s)
FROM Table_Name WHERE NOT EXISTS
(SELECT col_name(s)
FROM Table_Name
WHERE condition)
- Retrieve all those customers whose information is present in the Customer Table but didn't purchase any phone.
NOT EXISTS OUTPUT
4. LEFT JOIN AND IS NULL
SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.column_match=Table2.column_match
WHERE Table2.col_name IS NULL
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
LEFT JOIN AND IS NULLL
Similarly, we can do it for all the other JOINS in SQL. It is generally used with WHERE Clause to get the rows that have NULL entry after joining two or more tables.
Similar Reads
WordPress Optimization Does your WordPress website load slowly? Are visitors struggling to find your pages? Keeping your site fast and optimized is crucial. A well-optimized WordPress site not only loads quickly but also ranks better in search engines and provides visitors with a great experience. In this article, we'll c
5 min read
PostgreSQL - Managing Views Views in PostgreSQL are a powerful tool that allows you to simplify complex queries by abstracting them into virtual tables. They are derived from base tables or previously defined views, offering a convenient way to organize and manage your data without creating physical copies. In this article, we
4 min read
PostgreSQL - ALL Operator The PostgreSQL ALL operator is a powerful tool for comparing a value with a list of values returned by a subquery. This operator is essential for filtering and querying data based on comparisons with multiple values, making it a valuable addition to any PostgreSQL user's toolkit.Let us better unders
3 min read
PostgreSQL EXPLAIN Statement When we work with large databases, query performance becomes critically important to ensure efficient data retrieval. One of the most effective tools to analyze and improve query performance in PostgreSQL is the EXPLAIN statement. EXPLAIN provides detailed information on how PostgreSQL executes quer
4 min read
Optimizing PostgreSQL Database Performance PostgreSQL is a powerful open-source relational database management system known for its reliability, robust feature set, and extensibility. However, as with any database system, optimizing performance is crucial to ensure efficient data operations and responsiveness. In this article, we'll explore
3 min read
PostgreSQL - CREATE INDEX The PostgreSQL CREATE INDEX statement is essential for improving database performance, allowing faster data retrieval by creating indexes on specified columns. Indexes in PostgreSQL act like pointers, significantly reducing the time required for query processing, especially on large tables. In this
5 min read
PostgreSQL - Multicolumn Indexes In PostgreSQL, multicolumn indexes, also known as composite indexes, combined indexes, or concatenated indexes, are indexes defined on more than one column of a table. These indexes can significantly improve query performance by allowing the database to quickly locate rows based on multiple column v
3 min read
PostgreSQL - Alias PostgreSQL aliases are powerful tools that allow you to assign temporary names to tables or columns within your queries. These aliases only exist during the execution of the query, making your SQL code more readable and efficient.What is a PostgreSQL Alias?An alias in PostgreSQL is a temporary name
2 min read
PostgreSQL - LIMIT clause The PostgreSQL LIMIT clause is a handy tool used to fetch a specific subset of rows returned by a query. This clause is optional and can be a powerful way to control the amount of data your query returns, especially when working with large datasets. Let us better understand the LIMIT Clause in Postg
2 min read
Group By Vs Distinct in PostgreSQL When working with PostgreSQL, efficiently organizing and retrieving data is critical for database performance and decision-making. Two commonly used clauses are DISTINCT and GROUP BY that serve distinct purposes in data retrieval. DISTINCT is used to filter out duplicate values, while GROUP BY is em
6 min read