Open In App

PostgreSQL – SQL Optimization

Improve
Improve
Like Article
Like
Save
Share
Report

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
                   Customer Information
Customer ID Customer Name Email Address
1 Srishti abc@gmail.com
2 Rajdeep def@gmail.com
3 Aman  xxx@gmail.com
4 Pooja xyz@gmail.com
5 Tanmoy tdq@gmail.com

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 column
SELECT 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.



Last Updated : 07 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads