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=# 	iming 
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,014 ms

Unlike UNION ALL, the UNION statement eliminates all duplicates in the final result set. Due to this, there is no need to do sorting and filtering several times.

Another common mistake is to use ORDER BY in a view definition. If ORDER BY is used when selecting data from the view, it also introduces unnecessary sort operations, as in the following query:

postgres=# CREATE OR REPLACE VIEW guru_vw AS SELECT * FROM guru order by 1 asc;
CREATE VIEW
Time: 42,370 ms
postgres=# 
postgres=# SELECT * FROM guru_vw;
Time: 132,292 ms

In the preceding examples, retrieving data from the view takes 132 ms, while retrieving data from the table directly takes only 85 ms.