Optimize the grouping behavior when finding by large path

[nbekirov]

Hello!

While testing how closure_tree is handling deep nesting for a project I noticed that at some point it started to issue an unreasonably large number of SQL queries. Looking at the max_join_tables (currently 50) I assumed that for 55 levels of nesting there will be something like 2 queries and not 50!

I tracked down the issue to the use of in_groups which splits the path array in "(the given) number of groups" and replaced it with in_groups_of which would rather split "in groups of (the given) size".

Check out the updated spec example SQL output:

• before (20 queries)

# max_join_tables = 20
# path = [{"name"=>"1"}, {"name"=>"2"}, {"name"=>"3"}, {"name"=>"4"}, {"name"=>"5"}, {"name"=>"6"}, {"name"=>"7"}, {"name"=>"8"}, {"name"=>"9"}, {"name"=>"10"}, {"name"=>"11"}, {"name"=>"12"}, {"name"=>"13"}, {"name"=>"14"}, {"name"=>"15"}, {"name"=>"16"}, {"name"=>"17"}, {"name"=>"18"}, {"name"=>"19"}, {"name"=>"20"}, {"name"=>"21"}, {"name"=>"22"}, {"name"=>"23"}, {"name"=>"24"}, {"name"=>"25"}, {"name"=>"26"}, {"name"=>"27"}, {"name"=>"28"}, {"name"=>"29"}, {"name"=>"30"}, {"name"=>"31"}, {"name"=>"32"}, {"name"=>"33"}, {"name"=>"34"}, {"name"=>"35"}, {"name"=>"36"}, {"name"=>"37"}, {"name"=>"38"}, {"name"=>"39"}, {"name"=>"40"}, {"name"=>"41"}, {"name"=>"42"}, {"name"=>"43"}, {"name"=>"44"}, {"name"=>"45"}, {"name"=>"46"}, {"name"=>"47"}, {"name"=>"48"}, {"name"=>"49"}, {"name"=>"50"}, {"name"=>"51"}, {"name"=>"52"}, {"name"=>"53"}, {"name"=>"54"}, {"name"=>"55"}, {"name"=>"56"}, {"name"=>"57"}, {"name"=>"58"}, {"name"=>"59"}, {"name"=>"60"}, {"name"=>"61"}, {"name"=>"62"}, {"name"=>"63"}, {"name"=>"64"}, {"name"=>"65"}, {"name"=>"66"}, {"name"=>"67"}, {"name"=>"68"}, {"name"=>"69"}, {"name"=>"70"}]
# subpath = [{"name"=>"1"}, {"name"=>"2"}, {"name"=>"3"}, {"name"=>"4"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" IS NULL ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"5"}, {"name"=>"6"}, {"name"=>"7"}, {"name"=>"8"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"9"}, {"name"=>"10"}, {"name"=>"11"}, {"name"=>"12"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"13"}, {"name"=>"14"}, {"name"=>"15"}, {"name"=>"16"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"17"}, {"name"=>"18"}, {"name"=>"19"}, {"name"=>"20"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"21"}, {"name"=>"22"}, {"name"=>"23"}, {"name"=>"24"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"25"}, {"name"=>"26"}, {"name"=>"27"}, {"name"=>"28"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"29"}, {"name"=>"30"}, {"name"=>"31"}, {"name"=>"32"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"33"}, {"name"=>"34"}, {"name"=>"35"}, {"name"=>"36"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"37"}, {"name"=>"38"}, {"name"=>"39"}, {"name"=>"40"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p2"."parent_id" = $5 ORDER BY "tags"."id" ASC LIMIT $6

# subpath = [{"name"=>"41"}, {"name"=>"42"}, {"name"=>"43"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"44"}, {"name"=>"45"}, {"name"=>"46"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"47"}, {"name"=>"48"}, {"name"=>"49"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"50"}, {"name"=>"51"}, {"name"=>"52"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"53"}, {"name"=>"54"}, {"name"=>"55"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"56"}, {"name"=>"57"}, {"name"=>"58"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"59"}, {"name"=>"60"}, {"name"=>"61"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"62"}, {"name"=>"63"}, {"name"=>"64"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"65"}, {"name"=>"66"}, {"name"=>"67"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

# subpath = [{"name"=>"68"}, {"name"=>"69"}, {"name"=>"70"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p1"."parent_id" = $4 ORDER BY "tags"."id" ASC LIMIT $5

• and after the change (4 queries)

# max_join_tables = 20
# path = [{"name"=>"1"}, {"name"=>"2"}, {"name"=>"3"}, {"name"=>"4"}, {"name"=>"5"}, {"name"=>"6"}, {"name"=>"7"}, {"name"=>"8"}, {"name"=>"9"}, {"name"=>"10"}, {"name"=>"11"}, {"name"=>"12"}, {"name"=>"13"}, {"name"=>"14"}, {"name"=>"15"}, {"name"=>"16"}, {"name"=>"17"}, {"name"=>"18"}, {"name"=>"19"}, {"name"=>"20"}, {"name"=>"21"}, {"name"=>"22"}, {"name"=>"23"}, {"name"=>"24"}, {"name"=>"25"}, {"name"=>"26"}, {"name"=>"27"}, {"name"=>"28"}, {"name"=>"29"}, {"name"=>"30"}, {"name"=>"31"}, {"name"=>"32"}, {"name"=>"33"}, {"name"=>"34"}, {"name"=>"35"}, {"name"=>"36"}, {"name"=>"37"}, {"name"=>"38"}, {"name"=>"39"}, {"name"=>"40"}, {"name"=>"41"}, {"name"=>"42"}, {"name"=>"43"}, {"name"=>"44"}, {"name"=>"45"}, {"name"=>"46"}, {"name"=>"47"}, {"name"=>"48"}, {"name"=>"49"}, {"name"=>"50"}, {"name"=>"51"}, {"name"=>"52"}, {"name"=>"53"}, {"name"=>"54"}, {"name"=>"55"}, {"name"=>"56"}, {"name"=>"57"}, {"name"=>"58"}, {"name"=>"59"}, {"name"=>"60"}, {"name"=>"61"}, {"name"=>"62"}, {"name"=>"63"}, {"name"=>"64"}, {"name"=>"65"}, {"name"=>"66"}, {"name"=>"67"}, {"name"=>"68"}, {"name"=>"69"}, {"name"=>"70"}]
# subpath = [{"name"=>"1"}, {"name"=>"2"}, {"name"=>"3"}, {"name"=>"4"}, {"name"=>"5"}, {"name"=>"6"}, {"name"=>"7"}, {"name"=>"8"}, {"name"=>"9"}, {"name"=>"10"}, {"name"=>"11"}, {"name"=>"12"}, {"name"=>"13"}, {"name"=>"14"}, {"name"=>"15"}, {"name"=>"16"}, {"name"=>"17"}, {"name"=>"18"}, {"name"=>"19"}, {"name"=>"20"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" INNER JOIN "tags" AS p3
             ON p3."id" =
"p2"."parent_id" INNER JOIN "tags" AS p4
             ON p4."id" =
"p3"."parent_id" INNER JOIN "tags" AS p5
             ON p5."id" =
"p4"."parent_id" INNER JOIN "tags" AS p6
             ON p6."id" =
"p5"."parent_id" INNER JOIN "tags" AS p7
             ON p7."id" =
"p6"."parent_id" INNER JOIN "tags" AS p8
             ON p8."id" =
"p7"."parent_id" INNER JOIN "tags" AS p9
             ON p9."id" =
"p8"."parent_id" INNER JOIN "tags" AS p10
             ON p10."id" =
"p9"."parent_id" INNER JOIN "tags" AS p11
             ON p11."id" =
"p10"."parent_id" INNER JOIN "tags" AS p12
             ON p12."id" =
"p11"."parent_id" INNER JOIN "tags" AS p13
             ON p13."id" =
"p12"."parent_id" INNER JOIN "tags" AS p14
             ON p14."id" =
"p13"."parent_id" INNER JOIN "tags" AS p15
             ON p15."id" =
"p14"."parent_id" INNER JOIN "tags" AS p16
             ON p16."id" =
"p15"."parent_id" INNER JOIN "tags" AS p17
             ON p17."id" =
"p16"."parent_id" INNER JOIN "tags" AS p18
             ON p18."id" =
"p17"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p3"."name" = $5 AND "p4"."name" = $6 AND "p5"."name" = $7 AND "p6"."name" = $8 AND "p7"."name" = $9 AND "p8"."name" = $10 AND "p9"."name" = $11 AND "p10"."name" = $12 AND "p11"."name" = $13 AND "p12"."name" = $14 AND "p13"."name" = $15 AND "p14"."name" = $16 AND "p15"."name" = $17 AND "p16"."name" = $18 AND "p17"."name" = $19 AND "p18"."name" = $20 AND "p18"."parent_id" IS NULL ORDER BY "tags"."id" ASC LIMIT $21

# subpath = [{"name"=>"21"}, {"name"=>"22"}, {"name"=>"23"}, {"name"=>"24"}, {"name"=>"25"}, {"name"=>"26"}, {"name"=>"27"}, {"name"=>"28"}, {"name"=>"29"}, {"name"=>"30"}, {"name"=>"31"}, {"name"=>"32"}, {"name"=>"33"}, {"name"=>"34"}, {"name"=>"35"}, {"name"=>"36"}, {"name"=>"37"}, {"name"=>"38"}, {"name"=>"39"}, {"name"=>"40"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" INNER JOIN "tags" AS p3
             ON p3."id" =
"p2"."parent_id" INNER JOIN "tags" AS p4
             ON p4."id" =
"p3"."parent_id" INNER JOIN "tags" AS p5
             ON p5."id" =
"p4"."parent_id" INNER JOIN "tags" AS p6
             ON p6."id" =
"p5"."parent_id" INNER JOIN "tags" AS p7
             ON p7."id" =
"p6"."parent_id" INNER JOIN "tags" AS p8
             ON p8."id" =
"p7"."parent_id" INNER JOIN "tags" AS p9
             ON p9."id" =
"p8"."parent_id" INNER JOIN "tags" AS p10
             ON p10."id" =
"p9"."parent_id" INNER JOIN "tags" AS p11
             ON p11."id" =
"p10"."parent_id" INNER JOIN "tags" AS p12
             ON p12."id" =
"p11"."parent_id" INNER JOIN "tags" AS p13
             ON p13."id" =
"p12"."parent_id" INNER JOIN "tags" AS p14
             ON p14."id" =
"p13"."parent_id" INNER JOIN "tags" AS p15
             ON p15."id" =
"p14"."parent_id" INNER JOIN "tags" AS p16
             ON p16."id" =
"p15"."parent_id" INNER JOIN "tags" AS p17
             ON p17."id" =
"p16"."parent_id" INNER JOIN "tags" AS p18
             ON p18."id" =
"p17"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p3"."name" = $5 AND "p4"."name" = $6 AND "p5"."name" = $7 AND "p6"."name" = $8 AND "p7"."name" = $9 AND "p8"."name" = $10 AND "p9"."name" = $11 AND "p10"."name" = $12 AND "p11"."name" = $13 AND "p12"."name" = $14 AND "p13"."name" = $15 AND "p14"."name" = $16 AND "p15"."name" = $17 AND "p16"."name" = $18 AND "p17"."name" = $19 AND "p18"."name" = $20 AND "p18"."parent_id" = $21 ORDER BY "tags"."id" ASC LIMIT $22

# subpath = [{"name"=>"41"}, {"name"=>"42"}, {"name"=>"43"}, {"name"=>"44"}, {"name"=>"45"}, {"name"=>"46"}, {"name"=>"47"}, {"name"=>"48"}, {"name"=>"49"}, {"name"=>"50"}, {"name"=>"51"}, {"name"=>"52"}, {"name"=>"53"}, {"name"=>"54"}, {"name"=>"55"}, {"name"=>"56"}, {"name"=>"57"}, {"name"=>"58"}, {"name"=>"59"}, {"name"=>"60"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" INNER JOIN "tags" AS p3
             ON p3."id" =
"p2"."parent_id" INNER JOIN "tags" AS p4
             ON p4."id" =
"p3"."parent_id" INNER JOIN "tags" AS p5
             ON p5."id" =
"p4"."parent_id" INNER JOIN "tags" AS p6
             ON p6."id" =
"p5"."parent_id" INNER JOIN "tags" AS p7
             ON p7."id" =
"p6"."parent_id" INNER JOIN "tags" AS p8
             ON p8."id" =
"p7"."parent_id" INNER JOIN "tags" AS p9
             ON p9."id" =
"p8"."parent_id" INNER JOIN "tags" AS p10
             ON p10."id" =
"p9"."parent_id" INNER JOIN "tags" AS p11
             ON p11."id" =
"p10"."parent_id" INNER JOIN "tags" AS p12
             ON p12."id" =
"p11"."parent_id" INNER JOIN "tags" AS p13
             ON p13."id" =
"p12"."parent_id" INNER JOIN "tags" AS p14
             ON p14."id" =
"p13"."parent_id" INNER JOIN "tags" AS p15
             ON p15."id" =
"p14"."parent_id" INNER JOIN "tags" AS p16
             ON p16."id" =
"p15"."parent_id" INNER JOIN "tags" AS p17
             ON p17."id" =
"p16"."parent_id" INNER JOIN "tags" AS p18
             ON p18."id" =
"p17"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p3"."name" = $5 AND "p4"."name" = $6 AND "p5"."name" = $7 AND "p6"."name" = $8 AND "p7"."name" = $9 AND "p8"."name" = $10 AND "p9"."name" = $11 AND "p10"."name" = $12 AND "p11"."name" = $13 AND "p12"."name" = $14 AND "p13"."name" = $15 AND "p14"."name" = $16 AND "p15"."name" = $17 AND "p16"."name" = $18 AND "p17"."name" = $19 AND "p18"."name" = $20 AND "p18"."parent_id" = $21 ORDER BY "tags"."id" ASC LIMIT $22

# subpath = [{"name"=>"61"}, {"name"=>"62"}, {"name"=>"63"}, {"name"=>"64"}, {"name"=>"65"}, {"name"=>"66"}, {"name"=>"67"}, {"name"=>"68"}, {"name"=>"69"}, {"name"=>"70"}]
SELECT  "tags".* FROM "tags" INNER JOIN "tags" AS p0
             ON p0."id" =
"tags"."parent_id" INNER JOIN "tags" AS p1
             ON p1."id" =
"p0"."parent_id" INNER JOIN "tags" AS p2
             ON p2."id" =
"p1"."parent_id" INNER JOIN "tags" AS p3
             ON p3."id" =
"p2"."parent_id" INNER JOIN "tags" AS p4
             ON p4."id" =
"p3"."parent_id" INNER JOIN "tags" AS p5
             ON p5."id" =
"p4"."parent_id" INNER JOIN "tags" AS p6
             ON p6."id" =
"p5"."parent_id" INNER JOIN "tags" AS p7
             ON p7."id" =
"p6"."parent_id" INNER JOIN "tags" AS p8
             ON p8."id" =
"p7"."parent_id" WHERE "tags"."name" = $1 AND "p0"."name" = $2 AND "p1"."name" = $3 AND "p2"."name" = $4 AND "p3"."name" = $5 AND "p4"."name" = $6 AND "p5"."name" = $7 AND "p6"."name" = $8 AND "p7"."name" = $9 AND "p8"."name" = $10 AND "p8"."parent_id" = $11 ORDER BY "tags"."id" ASC LIMIT $12

[seuros]

That some pretty good optimization. Did you check the cost of the both queries ?

[nbekirov]

@seuros, no actually.

For me, it was enough that path size of 49 was not calling find_by_large_path thus keeping it all to a single query (with ~50 inner-joins) and then going up by just 2 to 51 levels of nesting suddenly started spamming the DB in an unexpected manner.

I may check the performance of the big SQL under Postgres for example if we want to reduce the max_join_tables. But again, IMO those are 2 different optimizations.

[seuros]

Since you have the data already populated, can you append EXPLAIN to the query in psql. You will get the query cost there. Else i will try to do it this weekend to release a minor version on rubygems.

[nbekirov]

Sorry, already out office. ☺️

I’m available next Tuesday and will try if you haven’t during the weekend.

[nbekirov]

Now that I think of it, why would we even keep the if checking for “large paths”. We can just always split in groups of size n. For n<50 it’d behave entirely as it used to.

P.s. Is there any chance backporting this to 6.x? I noticed that 7 has some new gem requirements that are preventing me to use the new version in my current project.

[nbekirov]

@seuros, here's the promised data:

• rails c log before the change (using in_groups)
  • in_groups.log
• after the change (using in_groups_of)
  • in_groups_of.log
• along with the DB explain of the first group big query
  • in_groups_of_first_batch_explain.txt
  • also available as a gist
• and just in case explain analyze
  • in_groups_of_first_batch_explain_analyze.txt

[seuros]

Can you add a Changelog entry to this PR ? Add version 7.1.0

[nbekirov]

Done :)

[nbekirov]

Hello!

Can I help with anything more on this one?