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

From Matheus de Oliveira
Subject Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
Date
Msg-id CAJghg4J_t-kHmSky0LNVjSmtF=7Vq5FMYAoZ82fcCeTSZCrxOA@mail.gmail.com
Whole thread Raw
In response to Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8  ("Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@nasa.gov>)
Responses Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
List pgsql-admin


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.

Best regards,
--
Matheus de Oliveira


pgsql-admin by date:

Previous
From: "Wilson, Maria Louise (LARC-E301)[RSES]"
Date:
Subject: Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
Next
From: Ron Johnson
Date:
Subject: Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8