The SQL that EOModeler generates for many to many join tables does not allow full optimization of the join. Many to many join tables are only generated with a single index which is the compound key of both columns.
CREATE TABLE "GROUP_FILE" (
"FILE_PKEY" NUMERIC NOT NULL,
"GROUP_PKEY" NUMERIC NOT NULL
ALTER TABLE "GROUP_FILE"
NOT DEFERRABLE INITIALLY IMMEDIATE;
This index can only be used to optimize joins on the first column in the index (which is the second column in the table for some reason). The lack of an index for the other column results in a table scan when joining on the other column. This sort of joins happen when following a to many relationship. For example, group.files() will result in SQL similar to this for each element in files():
FROM FILE f,
GROUP g, GROUP_FILE gf
WHERE f.PKEY = gf.FILE_PKEYAND
AND g.PKEY = gf.GROUP_PKEYAND
AND g.PKEY = 10;
Only the GROUP_PKEY has an index which is usable for optimizing this query. The join on FILE_PKEY (f.PKEY = gf.FILE_PKEY) is not optimized and results in table scans. You need to manually add an index for this:
CREATE INDEX GROUP_FILE_FILE_PKEY
ON GROUP_FILE (FILE_PKEY);
This can make a truly dramatic difference if there are a lot of rows in the join table.