Is select distinct slow?
Very few queries may perform faster in SELECT DISTINCT mode, and very few will perform slower (but not significantly slower) in SELECT DISTINCT mode but for the later case it is likely that the application may need to examine the duplicate cases, which shifts the performance and complexity burden to the application.
Is select distinct bad practice?
As a general rule, SELECT DISTINCT incurs a fair amount of overhead for the query. Hence, you should avoid it or use it sparingly. The idea of generating duplicate rows using JOIN just to remove them with SELECT DISTINCT is rather reminiscent of Sisyphus pushing a rock up a hill, only to have it roll back down again.
How to speed up a SELECT DISTINCT query?
A lot will depend on the details of the query and the tables involved– your saying you “know the problem is with the DISTINCT” really limits the scope of available answers. Oftentimes, you can make such queries run faster by working around the distinct by using a group by instead:
When to use SELECT DISTINCT in pubs query?
Speeding Up SELECT DISTINCT Queries. Many people use the DISTINCT option in a select statement to filter out duplicate results from a query’s output. Take this simple PUBS database query as an example: In a simple select from one table (like the one above) this is the easiest and quickest way of doing things.
How many rows does a distinct query need?
The table in question has 2.5 million rows of data. The DISTINCT is needed for purposes not listed here (because I don’t want back a modified query, but rather just general information about making distinct queries run faster at the DBMS level, if possible).
Why is my distinct query so slow in Postgres?
Your DISTINCT is causing it to sort the output rows in order to find duplicates. If you put an index on the column (s) selected by the query, the database may be able to read them out in index order and save the sort step.