From: Jan Wieck <janwieck@yahoo.com>
To: [Ikl_di] Lajos <il@mithrandir.hu>
Cc: Jan Wieck <janwieck@yahoo.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Cc: William Meloney <bmeloney@mindspring.com>
Subject: Re: Update in trigger
Date: Thu, 4 Apr 2002 10:05:47 -0500 (EST)
Message-ID: <200204041505.g34F5l807360@saturn.janwieck.net> (raw)
In-Reply-To: <3CABCB62.6E94216@mithrandir.hu>
[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@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janwieck@yahoo.com, il@mithrandir.hu, bmeloney@mindspring.com
Subject: Re: Update in trigger
In-Reply-To: <200204041505.g34F5l807360@saturn.janwieck.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox