You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Efficiency of generated SQL for has_many *_not_eq
#1761
This follows from some quite extensive use of complex querying within our PostgreSQL database, and in particular searches for existing and not-existing records.
On a has_many, has_and_belongs_to_many or has_many :through association, a negative predicate (not_eq, not_cont, not_in, not_start and the rest) means no associated record matches the positive form. It is built as a correlated subquery rather than a join:
Person.ransack(articles_title_not_eq: 'Draft').result.to_sql
# ... WHERE "people"."id" NOT IN (
# SELECT "articles"."person_id" FROM "articles"
# WHERE "articles"."person_id" = "people"."id"
# AND NOT ("articles"."title" != 'Draft'))
I wanted to observe first that a correlated subquery would generally be of the form where exists/not exists ( ... join_predicate), and an uncorrelated subquery would be where in/not in ( ... no_join_predicates), but the example shown (and the example generated in our tests) is of the form where in/not in ( ... join_predicate).
That seems very unusual, and I compared the execution plans for three options using our own schema and data, which has a table works and sale_summary_works – all of these methods giving exactly the same result of 133,953.
PostgreSQL 17.4 on Macos 27.0.1
(dev)> Work.count
Work Count (41.7ms) SELECT COUNT(*) FROM "works"
=> 134038
(dev)> Sale::Summary::Work.count
Sale::Summary::Work Count (4.5ms) SELECT COUNT(*) FROM "sale_summary_works"
=> 15517
(dev)> Sale::Summary::Work.where(sale_value: 0).count
Sale::Summary::Work Count (4.0ms) SELECT COUNT(*) FROM "sale_summary_works" WHERE "sale_summary_works"."sale_value" = 0.0
=> 85
Current Ransack behaviour
cost=0.42..583121.60 rows=67246
execution times 80.059 ms 67.355 ms 73.366 ms
(dev)> Work.ransack(sale_summary_works_sale_value_not_eq: 0).result.select(:id).explain
Work Load (77.6ms) SELECT "works"."id" FROM "works" WHERE "works"."id" NOT IN (SELECT "sale_summary_works"."work_id" FROM "sale_summary_works" WHERE "sale_summary_works"."work_id" = "works"."id" AND NOT ("sale_summary_works"."sale_value" != 0.0))
=>
EXPLAIN SELECT "works"."id" FROM "works" WHERE "works"."id" NOT IN (SELECT "sale_summary_works"."work_id" FROM "sale_summary_works" WHERE "sale_summary_works"."work_id" = "works"."id" AND NOT ("sale_summary_works"."sale_value" != 0.0))
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Only Scan using works_pkey on works (cost=0.42..583121.60 rows=67246 width=4)
Filter: (NOT (ANY (id = (SubPlan 1).col1)))
SubPlan 1
-> Index Scan using index_sale_summary_works_on_work_id on sale_summary_works (cost=0.29..8.30 rows=1 width=4)
Index Cond: (work_id = works.id)
Filter: (sale_value = 0.0)
(6 rows)
Conventional uncorrelated subquery
cost=401.59..5707.22 rows=67246
execution times 38.406 ms 38.613 ms 38.090 ms
(dev)> Work.where.not(id: Sale::Summary::Work.where(sale_value: 0).select(:work_id)).select(:id).explain
Work Load (34.2ms) SELECT "works"."id" FROM "works" WHERE "works"."id" NOT IN (SELECT "sale_summary_works"."work_id" FROM "sale_summary_works" WHERE "sale_summary_works"."sale_value" = 0.0) conso
=>
EXPLAIN SELECT "works"."id" FROM "works" WHERE "works"."id" NOT IN (SELECT "sale_summary_works"."work_id" FROM "sale_summary_works" WHERE "sale_summary_works"."sale_value" = 0.0)
QUERY PLAN
--------------------------------------------------------------------------------------
Index Only Scan using works_pkey on works (cost=401.59..5707.22 rows=67246 width=4)
Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))
SubPlan 1
-> Seq Scan on sale_summary_works (cost=0.00..400.96 rows=85 width=4)
Filter: (sale_value = 0.0)
(5 rows)
Conventional correlated subquery
cost=404.11..5711.01 rows=134408
execution times 30.122 ms 34.036 ms 29.384 ms
(dev)> Work.where.not(Sale::Summary::Work.where("works.id = sale_summary_works.work_id").where(sale_value: 0).arel.exists).select(:id).explain
Work Load (33.8ms) SELECT "works"."id" FROM "works" WHERE NOT (EXISTS (SELECT "sale_summary_works".* FROM "sale_summary_works" WHERE (works.id = sale_summary_works.work_id) AND "sale_summary_works"."sale_value" = 0.0))
=>
EXPLAIN SELECT "works"."id" FROM "works" WHERE NOT (EXISTS (SELECT "sale_summary_works".* FROM "sale_summary_works" WHERE (works.id = sale_summary_works.work_id) AND "sale_summary_works"."sale_value" = 0.0))
QUERY PLAN
-------------------------------------------------------------------------------------------
Merge Anti Join (cost=404.11..5711.01 rows=134408 width=4)
Merge Cond: (works.id = sale_summary_works.work_id)
-> Index Only Scan using works_pkey on works (cost=0.42..4969.82 rows=134493 width=4)
-> Sort (cost=403.69..403.90 rows=85 width=4)
Sort Key: sale_summary_works.work_id
-> Seq Scan on sale_summary_works (cost=0.00..400.96 rows=85 width=4)
Filter: (sale_value = 0.0)
(7 rows)
Some thoughts
It feels like the current method, for PostgreSQL at least, might be the worst of the three options. I've tried a couple of other cases which did not make so much of a difference between the properly correlated and properly uncorrelated versions, but they were still considerably better than the current Ransack implementation.
It could be that this is PostgreSQL specific, but I don't have other systems (or the expertise) to investigate those.
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
Uh oh!
There was an error while loading. Please reload this page.
Hi all,
Ransack 4.3.0
This follows from some quite extensive use of complex querying within our PostgreSQL database, and in particular searches for existing and not-existing records.
Quoting from the Ransack docs on negative predicates on collections:
I wanted to observe first that a correlated subquery would generally be of the form
where exists/not exists ( ... join_predicate), and an uncorrelated subquery would bewhere in/not in ( ... no_join_predicates), but the example shown (and the example generated in our tests) is of the formwhere in/not in ( ... join_predicate).That seems very unusual, and I compared the execution plans for three options using our own schema and data, which has a table
worksandsale_summary_works– all of these methods giving exactly the same result of 133,953.PostgreSQL 17.4 on Macos 27.0.1
Current Ransack behaviour
cost=0.42..583121.60 rows=67246
execution times 80.059 ms 67.355 ms 73.366 ms
Conventional uncorrelated subquery
cost=401.59..5707.22 rows=67246
execution times 38.406 ms 38.613 ms 38.090 ms
Conventional correlated subquery
cost=404.11..5711.01 rows=134408
execution times 30.122 ms 34.036 ms 29.384 ms
Some thoughts
It feels like the current method, for PostgreSQL at least, might be the worst of the three options. I've tried a couple of other cases which did not make so much of a difference between the properly correlated and properly uncorrelated versions, but they were still considerably better than the current Ransack implementation.
It could be that this is PostgreSQL specific, but I don't have other systems (or the expertise) to investigate those.
All reactions