On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jba...@agristats.com> wrote:
> 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.
