There are some common mistakes and bad practices that a developer may fall into, which are as follows:
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...