Re: performance with triggers depends on table size?

Christian Mock wrote:
>
> I can't seem to see any external influences; there is a once-a-night
> vacuum run from cron, which fits with one of the sawtooth "drops",
> but it still doesn't explain the other teeth. (And I checked the
> postgres log for signs of other vaccum runs, there are none).

I think you should be more aggressive on the vacuum. If you can, make
the nightly vacuum a vacuum full (requires full table lock, not always
an option). And I would further expect better results if you run a
normal vacuum about every hour.

> After an afternoon of playing around, I found I can keep the performance
> up (at least for 50000 rows) if I do
>
> BEGIN;
> INSERT INTO ac_event SELECT * FROM in_event WHERE eid < x AND eid > y;
> COMMIT;
> VACUUM ANALYZE event_stats;
>
> in a tight loop, where the number of rows selected by x and y is very
> small -- about 100 or less (and this is with the script not running
> on the DB machine).

There is something I have been thinking about (on earlier occasions as
well). For a trigger on this insert, the execution plan is determined on
the first insert and is then cached for the rest of the lifetime of the
connection. Right?
Wouldn't that mean that if you start with a very small event_stats
table, the planner would choose a seqscan and stick to that, even if the
planner should switch to an index scan after the event_stats table
reaches a certain size?

Maybe somebody can shed some light on this?

> Where's the big number of "Deleted" tuples coming from? The relevant part
> of the trigger function is below, the "DELETE" code path should never be
> chosen (and from watching the sequence for the stat_id column, which
> is completely in line with "count(*) from event_stats", it never is). Is
> the update (on the non-indexed column "count" only) causing this?

That would be a result of MVCC. The short version is that UPDATE's are
implemented as a combination of a DELETE and an INSERT. The long version
is at http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html
and http://www.postgresql.org/idocs/index.php?mvcc.html

Jochem