Re: One source of constant annoyance identified

On Tue, 2 Jul 2002, Tom Lane wrote:

> "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> > Sorry I took so long - I attached the schema as asked.
>
> Thanks. But I'm still unable to reproduce the memory bloat you see on
> SELECTs. This seems very peculiar. You said you were running SuSE 7.3
> --- how recent is that? Which glibc version is it running? (I've been
> reduced to speculating about memory leakage inside libc, which is a
> pretty long shot but...)
>
> > So used the already implemented trigger to
> > execute the fti-function:
>
> > update ct_com_board_message
> > set state_id=3D0
> > where state_id=3D0
> > and to_char(last_reply, 'yyyymmdd') between '20020301' and '20020830';
>
> > I took a quick look at top: Even this humble query causes memory- and
> > processor-load like a giant: 266M RAM, 38.3% processor time, 26.4%
> > memory usage. Okay, it's calling the trigger for each row which in turn
> > inserts some new tuples into ct_com_board_fti, but is it expected to
> > cause so much load?
>
> Wouldn't surprise me. Since you're using an AFTER trigger, the pending
> trigger events have to be saved up for commit time, so the list of
> pending events is going to grow quite large. (How many rows do you have
> in ct_com_board_message, anyway? How many did that query try to
> update?)

Whoa, that's what I was trying to remember. I had problems at one time
when loading a large amount of data into a table, with a txtidx type column. It
might not have been a memory problem I had though it could just have been slow
loading. I was loading with COPY in a transaction and ended up just doing the
COPY outside of a transaction. It still took a while but then it's only a low
powered machine.

If that wasn't the process footprint growing huge then that problem was
occuring for me when doing selects. I can't remember what it was that I did
that fixed it though. I wonder if it's in the list's archives since the issue
was raised here.

> This however does not explain your problem with SELECT, since
> selects don't fire triggers.
>
> Could I see the output of EXPLAIN for that problem SELECT on your
> machine?
>
> regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants