From: | "Wilson, Maria Louise (LARC-E301)[RSES]" <m(dot)l(dot)wilson(at)nasa(dot)gov> |
---|---|
To: | Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com> |
Cc: | Frits Hoogland <frits(dot)hoogland(at)gmail(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: [EXTERNAL] Need help with performance tuning pg12 on linux |
Date: | 2023-12-27 17:10:59 |
Message-ID: | 2C02F3A1-4125-47D8-8E9D-ED078AB006F7@nasa.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)matheus(at)gmail(dot)com>
Date: Wednesday, December 27, 2023 at 11:36 AM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m(dot)l(dot)wilson(at)nasa(dot)gov>
Cc: Frits Hoogland <frits(dot)hoogland(at)gmail(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)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.
-> Hash Join (cost=644250.54..10734700.30 rows=22333224 width=223) (actual time=7864.023..44546.392 rows=22325462 loops=1)
Hash Cond: (file_1.id<http://file_1.id/> = granule_file_1.file_id)
Buffers: shared hit=780882 read=8345236
-> Seq Scan on file file_1 (cost=0.00..9205050.88 rows=22068888 width=207) (actual time=402.706..25222.525 rows=22057988 loops=1)
Buffers: shared hit=639126 read=8345236
-> Hash (cost=365085.24..365085.24 rows=22333224 width=20) (actual time=7288.228..7288.235 rows=22325462 loops=1)
Buckets: 33554432 Batches: 1 Memory Usage: 1391822kB
Buffers: shared hit=141753
-> Seq Scan on granule_file granule_file_1 (cost=0.00..365085.24 rows=22333224 width=20) (actual time=0.030..2151.380 rows=22325462 loops=1)
Buffers: shared hit=141753
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?
Best regards,
Matheus de Oliveira
From | Date | Subject | |
---|---|---|---|
Next Message | Ranier Vilela | 2023-12-27 17:23:33 | Re: [EXTERNAL] Need help with performance tuning pg12 on linux |
Previous Message | Matheus de Oliveira | 2023-12-27 16:36:27 | Re: [EXTERNAL] Need help with performance tuning pg12 on linux |