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

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(dot)klaver(at)aklaver(dot)com>
> *Sent:* Monday, March 28, 2022 17:59
> *To:* Per Kaminsky <per(dot)kaminsky(at)hawk-intech(dot)com>;
> pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>; Tom Lane
> <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *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(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com