On Wed, Dec 27, 2023 at 2:23 PM Ranier Vilela wrote:
> ...
>
> Although granule_file has an index as a foreign key, it seems to me that
> it is not being considered.
>
You seem to be mistaken here, a foreign key does not automatically create
an index on the columns, you need to do it by
On Wed, Dec 27, 2023 at 2:11 PM Wilson, Maria Louise (LARC-E301)[RSES] <
m.l.wil...@nasa.gov> wrote:
> 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"
-> Seq Scan on visibility visibility_1 (cost=0.00..1.52
rows=52 width=16) (actual time=0.026..0.029 rows=52 loops=1)
Buffers: shared hit=1
Planning Time: 7.354 ms
Execution Time: 64789.927 ms
(52 rows)
From: Ranier Vilela
Date: Wednesday, December 27, 2023
Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise
(LARC-E301)[RSES] escreveu:
> 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
t;Wilson, Maria Louise (LARC-E301)[RSES]"
Cc: Frits Hoogland ,
"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
>
>-> 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 = granule_file_1.file_id)
> Buffers: shared hit=780882 read=8345236
>
Hi Maria, could you please run explain analyse for the problem query?
The ‘analyze’ addition will track actual spent time and show statistics to
validate the planner’s assumptions.
Frits Hoogland
> On 27 Dec 2023, at 16:38, Wilson, Maria Louise (LARC-E301)[RSES]
> wrote:
>
> Hello folks!
Hello folks!
I am having a complex query slowing over time increasing in duration. If
anyone has a few cycles that they could lend a hand or just point me in the
right direction with this – I would surely appreciate it! Fairly beefy Linux
server with Postgres 12 (latest) – this particular