JOIN SELECT DISTINCT ON slowing down query

Anton Hoerl 0
Viewed 27

I found this related question, but no answer.

I am making a query to my Postgres DB. Here is the code:

SELECT *
FROM setup s
FULL JOIN
  (SELECT *
   FROM part_model) pm ON s.p_m_part_id = pm.p_m_id
   FULL JOIN
  (SELECT *
   FROM part) p ON s.s_id = p.p_setup_id
JOIN setup_part_setting AS sps ON sps.created_at =
  (SELECT DISTINCT ON (created_at) created_at
   FROM setup_part_setting AS sps
   WHERE s.s_id = sps.s_setup_id
     AND p.p_id = sps.part_id
   ORDER BY sps.created_at DESC
   LIMIT 1)
WHERE ((brand_name ilike ANY (ARRAY [['%%']])))
  AND s.s_id IS NOT NULL
ORDER BY s.created_at DESC
LIMIT 5;

The query is bigger normally for for simplification I use this query in this question. The query works, but it is very slow. It took about ten seconds on my production database where there are more than 10.000 rows in the setup_part_setting table.

I tried to run this query without this part:

JOIN setup_part_setting AS sps ON sps.created_at =
  (SELECT DISTINCT ON (created_at) created_at
   FROM setup_part_setting AS sps
   WHERE s.s_id = sps.s_setup_id
     AND p.p_id = sps.part_id
   ORDER BY sps.created_at DESC
   LIMIT 1)

And the query is very fast. Maybe 80ms. So I am wondering if I replace this part with something faster.

This part should join one row in setup_part_setting table which has the same s_setup_id and part_id like from the selected setup table. This row should be the row with the latest created_at value.

Any help really appreciated.

query-optimization postgresql
1 Answers
  1. When you are use join (select * from table) p on p.id - this is subquery and join subqueries this is very slow. Use this: join table p on p.id format instead of join (select * from table) p on p.id
  2. distinct and limit 1, This is pointless. distinct only for multiple rows which may be duplicated. Remove distinct command.

Run this query separately outside and see the result:

SELECT created_at
   FROM setup_part_setting AS sps
   WHERE s.s_id = sps.s_setup_id
     AND p.p_id = sps.part_id
   ORDER BY sps.created_at DESC Limit 1

If the indexes are created correctly, this query should be run very fast

Also do explain and analyze your query, there should be index scan everywhere, not table seg scan

Ramin Faracov0
Related