Postgresql : how to speed up SELECT DISTINCT query?

Question

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?

Answer 1

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.