pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 #





view thread (4+ messages)

Message-ID: <200204041505.g34F5l807360@saturn.janwieck.net>
Permalink:  ../200204041505.g34F5l807360@saturn.janwieck.net/
Also on:    postgresql.org/message-id/200204041505.g34F5l807360@saturn.janwieck.net

 · 

reply

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