Query tuning

Before discussing query tuning, first we should know how to write a query, and then we can tune it later on. Queries can be written in multiple ways, as per the requirement; however, we have to prefer an optimal way to write queries.

For example, say that your requirement is to get the row count from a static table. You can achieve this through the following three methods:

Method 1:

postgres=# SELECT SUM(1) FROM customers;
sum
------
1000
(1 row)
Time: 4.308 ms

Method 2:

postgres=# SELECT COUNT(*) FROM customers;
count
-------
1000
(1 row)
Time: 3.128 ms

Method 3:

postgres=# SELECT reltuples FROM pg_class WHERE relname =  'customers';
reltuples 
-----------
1000
(1 row)
Time: 0.576 ms

From the preceding three possible methods, we see that Method 3 gives the best result, and Method 2 is optimal when compared to Method 1. Method 1 takes a bit more time, when compared with Method 2, since it needs to SUM all the records at once, whereas the COUNT aggregate follows a different...