+3 votes
in Databases by (6.3k 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 (25.6k 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 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. 

Related questions