https://explain.depesz.com/s/kyl1#html
that last index ---(CREATE INDEX ON granule_file(granule_uuid);
seemed to make the biggest difference!! Thank you!!
Working on getting this into our production database!!
From: Matheus de Oliveira <matioli.matheus@gmail.com>
Date: Thursday, December 28, 2023 at 2:38 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@nasa.gov>
Cc: "depesz@depesz.com" <depesz@depesz.com>, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
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. |
On Thu, Dec 28, 2023 at 3:39 PM Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov> wrote:
...
Granule_file:
public | granule_file | table | ims_api_writer | 1108 MB |
\d granule_file
Table "public.granule_file"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
granule_uuid | uuid | | |
file_id | integer | | |
Foreign-key constraints:
"granule_file_file_id_fkey" FOREIGN KEY (file_id) REFERENCES file(id)
"granule_file_granule_uuid_fkey" FOREIGN KEY (granule_uuid) REFERENCES granule(uuid)
You did not created the index I recommended before:
CREATE INDEX ON granule_file(granule_uuid);
Try creating this index and show us the new generated plan with `EXPLAIN (ANALYZE, VERBOSE, BUFFERS)` (you can paste it at depesz tool).
PS: As said in the other thread, a primary key on (granule_uuid, file_id) would make more sense, but you need to get rid of null values on these rows before creating the PK.
--