On 3/28/22 09:39, Per Kaminsky wrote:
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).
The ANALYZE would be more about telling the planner that the data in
tables changed and what they changed to, then anything to do with the
index. For this you really don't need the VERBOSE.
------------------------------------------------------------------------
*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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
