Optimization of construct_finder_sql_with_included_associations(options, join_dependency) ActiveRecord::Associations::ClassMethods

Hello.
I found that method construct_finder_sql_with_included_associations(options, join_dependency) of Rails 2.3 is not optimal when called from find() method with :order, :include and :limit options. It adds unnecessary checks in WHERE clause after load IDs for limited eager loading.

If we have already found needed IDs during limited eager loading, we don't need additional checks in WHERE clause.

Example.
Before patch

Education::Pc Load IDs For Limited Eager Loading (4.7ms)

SELECT  FROM (SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost, pcs.max_cost) AS alias_0 FROM "pcs" WHERE ((SELECT COUNT() FROM metro_stations_pcs c WHERE c.metro_station_id IN ('57') AND c.pc_id = pcs.id) > 0 AND (SELECT COUNT(*) FROM pcs_durations c WHERE c.duration_id IN ('4') AND c.pc_id = pcs.id) > 0) ) AS id_list ORDER BY id_list.alias_0 LIMIT 20 OFFSET 0

Education::Pc Load Including Associations (2.3ms)

SELECT "pcs"."id" AS t0_r0, "pcs"."name" AS t0_r1, "pcs"."university_id" AS t0_r2, "pcs"."has_distance_learning" AS t0_r3, "pcs"."has_sse_preparation" AS t0_r4, "pcs"."moscow_administrative_district" AS t0_r5, "pcs"."min_cost" AS t0_r6, "pcs"."max_cost" AS t0_r7, "pcs"."address" AS t0_r8, "pcs"."phones" AS t0_r9, "pcs"."emails" AS t0_r10, "pcs"."website" AS t0_r11, "pcs"."description" AS t0_r12, "pcs"."entering_information" AS t0_r13, "pcs"."cost_information" AS t0_r14, "pcs"."duration" AS t0_r15, "pcs"."created_at" AS t0_r16, "pcs"."updated_at" AS t0_r17, "metro_stations"."id" AS t1_r0, "metro_stations"."name" AS t1_r1, "metro_stations"."created_at" AS t1_r2, "metro_stations"."updated_at" AS t1_r3, "metro_stations"."universities_count" AS t1_r4, "metro_stations"."colleges_count" AS t1_r5, "metro_stations"."schools_count" AS t1_r6, "metro_stations"."kindergartens_count" AS t1_r7, "metro_stations"."pcs_count" AS t1_r8, "metro_stations"."secs_count" AS t1_r9 FROM "pcs" LEFT OUTER JOIN "metro_stations_pcs" ON "metro_stations_pcs".pc_id = "pcs".id LEFT OUTER JOIN "metro_stations" ON "metro_stations".id = "metro_stations_pcs".metro_station_id WHERE ((SELECT COUNT() FROM metro_stations_pcs c WHERE c.metro_station_id IN ('57') AND c.pc_id = pcs.id) > 0 AND (SELECT COUNT() FROM pcs_durations c WHERE c.duration_id IN ('4') AND c.pc_id = pcs.id) > 0) AND "pcs".id IN (469) ORDER BY COALESCE(pcs.min_cost, pcs.max_cost) ASC

Look at WHERE clause of last query

After patch Education::Pc Load Including Associations (1.7ms)
SELECT "pcs"."id" AS t0_r0, "pcs"."name" AS t0_r1, "pcs"."university_id" AS t0_r2, "pcs"."has_distance_learning" AS t0_r3, "pcs"."has_sse_preparation" AS t0_r4, "pcs"."moscow_administrative_district" AS t0_r5, "pcs"."min_cost" AS t0_r6, "pcs"."max_cost" AS t0_r7, "pcs"."address" AS t0_r8, "pcs"."phones" AS t0_r9, "pcs"."emails" AS t0_r10, "pcs"."website" AS t0_r11, "pcs"."description" AS t0_r12, "pcs"."entering_information" AS t0_r13, "pcs"."cost_information" AS t0_r14, "pcs"."duration" AS t0_r15, "pcs"."created_at" AS t0_r16, "pcs"."updated_at" AS t0_r17, "metro_stations"."id" AS t1_r0, "metro_stations"."name" AS t1_r1, "metro_stations"."created_at" AS t1_r2, "metro_stations"."updated_at" AS t1_r3, "metro_stations"."universities_count" AS t1_r4, "metro_stations"."colleges_count" AS t1_r5, "metro_stations"."schools_count" AS t1_r6, "metro_stations"."kindergartens_count" AS t1_r7, "metro_stations"."pcs_count" AS t1_r8, "metro_stations"."secs_count" AS t1_r9 FROM "pcs" LEFT OUTER JOIN "metro_stations_pcs" ON "metro_stations_pcs".pc_id = "pcs".id LEFT OUTER JOIN "metro_stations" ON "metro_stations".id = "metro_stations_pcs".metro_station_id WHERE "pcs".id IN (469) ORDER BY COALESCE(pcs.min_cost, pcs.max_cost) ASC

Look at WHERE clause. There is no additional params. Only IN operator. You can find more information if you will do EXPLAIN. I have run all tests and all is pretty well.

Thank. Here is a patch and I hope, that it will be useful.