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