I get the explain telling me that it's doing a table scan within a table scan on patch set files.
EXPLAIN for: SELECT "comments".* FROM "comments" WHERE ("comments"."patch_set_file_id" NOT IN (SELECT id FROM "patch_set_files"))
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on comments (cost=791.85..1068.94 rows=2764 width=226)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on patch_set_files (cost=0.00..722.68 rows=27668 width=4)`
I have no idea why, but maybe if we run this query a little differently?
Postgres doesn't seem to be picking up the index on the verify
verify_comments_and_patch_set_files_relationship
.If I run this on the test data set:
Comment.where.not(patch_set_file_id: PatchSetFile.select("id")).explain
I get the explain telling me that it's doing a table scan within a table scan on patch set files.
I have no idea why, but maybe if we run this query a little differently?
This might be helpful: http://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index