Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8

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(dot)matheus(at)gmail(dot)com>
Date: Thursday, December 28, 2023 at 2:38 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m(dot)l(dot)wilson(at)nasa(dot)gov>
Cc: "depesz(at)depesz(dot)com" <depesz(at)depesz(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)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(dot)l(dot)wilson(at)nasa(dot)gov<mailto:m(dot)l(dot)wilson(at)nasa(dot)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.

Best regards,
--
Matheus de Oliveira