Re: Refresh Materialized View Issue

On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jbauer(at)agristats(dot)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.