My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3.
There is not, after further investigation. There are these 4 indexes that involve id1, id2, and id3. Should I try creating an index on all three of the columns?
CREATE INDEX IF NOT EXISTS idx_large_table_id1
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id2
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id3
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
[snip]
I'd strongly think about creating such an index, since the current indices don't help much.
Adding id1 to the end of idx_large_table_id2_id3 might be a better option, since it'll reuse much of the existing disk space.
A materialized view isn't too different from an unlogged table.
So an unlogged table would also be an appropriate solution?
Sure, since materialized views are effectively tables with a SELECT statement bound to it, and are logged. Thus, unlogged tables are faster to create. Of course, being unlogged is a two-edged sword: any unclean shutdown makes the data go away; you'd have to rebuild the table.