Search Postgresql Archives

Re: Performance issues on FK Triggers after replacing a primary column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Do you mean a simple "ANALYZE VERBOSE A"? Or something different?
Following the thought that maybe the index got stale, i just tried to add a "REINDEX TABLE B". This did not help as well, which might be the case, if an index (re)build is always deferred until the end of the transaction (which i don't know if that is the case).


From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Monday, March 28, 2022 17:59
To: Per Kaminsky <per.kaminsky@xxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx>; Tom Lane <tgl@xxxxxxxxxxxxx>
Subject: Re: Performance issues on FK Triggers after replacing a primary column
 
On 3/28/22 08:47, Per Kaminsky wrote:
> The tables have Index to each other on each foreign key. The index
> itself was not touched though, and a remove/recreate did not help. Could
> it be possible, that when the PK and FK values are replaced the Index is
> not (immediately) updated and thus cannot be used?

Have you tried an ANALYZE on "A" AND "B" after?:

UPDATE "A" SET id = id_temp;

As to the index not immediately updating, I don't know.


>
> The temporary table is not shown. It is created to insert the new values
> from file, then used to update the correct table with the new values,
> and then removed, it has no connection (FK or something else) to any
> other table.
>

So that is the '// fill id_temp with new IDs' part?


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux