Thanks for the reply!! Having some issues due to nulls…. Any other thoughts?
i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);
ERROR:column "granule_uuid" contains null values
From: Matheus de Oliveira <matioli.matheus@gmail.com> Date: Wednesday, December 27, 2023 at 11:36 AM To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@nasa.gov> Cc: Frits Hoogland <frits.hoogland@gmail.com>, "pgsql-performance@lists.postgresql.org" <pgsql-performance@lists.postgresql.org> Subject: Re: [EXTERNAL] Need help with performance tuning pg12 on linux
CAUTION:This email originated from outside of NASA. Please take care when clicking links or opening attachments. Use the "Report Message" button to report suspicious messages to the NASA SOC.
This part above is the most expensive so far, and taking a look at your `granule_file` table on the first message, it has no indexes nor constraints, which certainly looks like a mistake. I'd start optimizing this, you could add an index on it, but seems that you need a primary key on both columns of this (junction?) table:
ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);
There are certainly more things to optimize on this query, but I prefer doing one thing at a time. Could you try with the PK and send the EXPLAIN ANALYZE of the query again after that?