Relevant portions of my DDL:

CREATE TABLE report_entries ( report_entry_id SERIAL PRIMARY KEY, ... );

CREATE TABLE bucket_joins ( deleted_id integer NOT NULL, report_entry_id integer NOT NULL, join_reason integer NOT NULL, PRIMARY KEY(deleted_id, report_entry_id) );

ALTER TABLE bucket_joins ADD CONSTRAINT fk_report_join FOREIGN KEY (report_entry_id) REFERENCES report_entries (report_entry_id) ON DELETE CASCADE ;

ALTER TABLE bucket_joins ADD CONSTRAINT fk_deleted_join FOREIGN KEY (deleted_id) REFERENCES report_entries (report_entry_id) ON DELETE CASCADE ;

I'm querying a group of rows from the report_entries table which includes the relevant soft-deleted rows, along with a prefetch to retrieve the collection of bucket_joins where report_entries.report_entry_id == bucket_join.report_entry_id.

I'm curious what the most efficient way to retrieve the ReportEntryEntity referenced by deleted_id because I know that I have already retrieved it in my initial query? I wasn't sure what mechanisms the runtime library use to avoid re-fetching an entity.

Thanks

This is how it works:

1. The main entities are fetches (ReportEntries)
2. The ids retrieved are stored in memory.
3. The second fetch (prefetchpath) take placed filtered by the ids of the main fetch (point 2).

In point (3) there are two ways:

select ... from bucket_joins ..... 
where report_entry_id IN
(
    @reportentry1, @reportentry2, .... @reportentryN)
)

or select ... from bucket_joins

where report_entry_id IN
(
    select report_entry_id 
    from report_entries
    where .... // the filter of the main query
)

The framework decides what query to use depending on the ParameterisedPrefetchPahTreshold. You can tweak this flag to meet your performance needs. Lear more about that in Optimizing Prefetch Paths