Re: Update in trigger

[Ikl_di] Lajos wrote:
[Charset iso-8859-2 unsupported, filtering to ASCII...]
> Hi,
>
> Jan Wieck _rta:
>
> > Are there appropriate indexes on the table updated in the
> > trigger and is the database "VACUUM ANALYZE"ed so the indexes
> > get used?
>
> There is a primary key.
> VACUUM ANALYZE is really helpful, but only for a few minutes (3000 inserted
> records).
>
>
> William Meloney _rta:
>
> > Please forward a copy of the trigger. This is exactly the same thing I
> > am trying to do but I have not had any success.
>
> Here is the whole structure:
>
> [...]

That's a well known problem with PostgreSQL's non-overwriting
storage management system. After your 3000 INSERTs, you also
have UPDATEed szm 3000 times, leaving 3000 dead rows and 3000
dead index entries in it. Thus, another UPDATE looking for
kod=1 will find 3000 index entries and has to check 3000 data
rows only to find that they have to be ignored.

The only way I see at the time beeing is to vacuum tables
that have very few rows with a high update frequency "very
often". No need to vacuum the rest of the database at the
same rate, just these special tables.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #