+2 votes
in Databases by (3.8k points)
I am using INNER JOIN to fetch values from 5 different tables, but I am getting some duplicate rows. When I use SELECT DISTINCT to select unique rows, the query becomes extremely slow. How can I speed up SELECT DISTINCT query on PostgreSQL database?

1 Answer

0 votes
by (16.9k points)

Never use SELECT DISTINCT when you are joining many tables. It slows down the query. I am not sure about the best way to speed up SELECT DISTINCT query. But the following trick really worked for me: "Use SELECT without DISTINCT when you are joining many tables and then use it as a subquery for SELECT DISTINCT * to fetch unique rows."

Here is an example:

SELECT DISTINCT *

  FROM(

  SELECT X1.a, X1.b, X1.c,

X2.d, X2.e,

X3.f, X3.g

FROM table1 X1, 

table1 X2, 

table1 X3 

   WHERE X1.a  = X2.d

AND X2.e = X3.f

) AS temptab;

The above query is way faster than running SELECT DISTINCT directly. 

...