Common mistakes in writing queries

There are some common mistakes and bad practices that a developer may fall into, which are as follows:

Unnecessary operations

There are different ways to introduce extra operations such as hard disk scans, sorting, and filtering. For example, some developers often use DISTINCT even if it is not required, or do not know the difference between UNION, UNION ALL, EXCEPT, EXCEPT ALL, and so on. This causes slow queries, especially if the expected number of rows is high. The following two queries are equivalent simply because the table has a primary key, but the one with DISTINCT is much slower:

postgres=# \timing 
Timing is on.
postgres=# SELECT * FROM guru;
Time: 85,089 ms
postgres=# SELECT DISTINCT * FROM guru;
Time: 191,335 ms

Another common mistake is to use DISTINCT with UNION, as in the following query:

postgres=# SELECT * FROM guru UNION SELECT * FROM guru;
Time: 267,258 ms
postgres=# SELECT DISTINCT * FROM guru UNION SELECT DISTINCT * FROM guru;
Time: 346...