Learning PostgreSQL 11 - Third Edition

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 isn't required, or they don't know the difference between UNION, UNION ALL, EXCEPT, and EXCEPT ALL. 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 mspostgres=# 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 ...