On 1/11/24 3:40 PM, Ron Johnson wrote:
On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jba...@agristats.com <mailto: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.

That'd be a band-aid at best, because we know that the query used to define the materialized view runs in a reasonable amount of time on it's own, as does a CTAS. So either the REFRESH is doing something odd when writing into the new relation (which looking at the code seems very unlikely), or REFRESH is getting a different query plan for some reason. Unfortunately, I don't know of any easy way to get the query plan for the REFRESH (it might be possible via gdb, but I'm not sure). We do at least know that the REFRESH is using parallel workers.

Can you post the output of EXPLAIN ANALYZE for the SELECT? That might provide some clues.
--
Jim Nasby, Data Architect, Austin TX



Reply via email to