Re: big transaction slows down over time - but disk seems

Ben wrote:
> My transaction calls the same stored procedure many times over. Over the
> lifetime of the transaction, that stored procedure slows down by roughly
> 2 orders of magnitude. The procedure itself tries to look up several
> strings in dictionary tables, and if the strings aren't there (most of
> them will be) it inserts them. All those dictionary tables have indexes.
> After it has converted most of the strings into ids, it does another
> lookup on a table and if it finds a matching row (should be the common
> case) it updates a timestamp column of that row; otherwise, it inserts a
> new row.

Which would suggest Heikki's guess was pretty much right and it's dead
rows that are causing the problem.

Assuming most updates are to this timestamp, could you try a test case
that does everything *except* update the timestamp. If that runs
blazingly fast then we've found the problem.

If that is the problem, there's two areas to look at:
1. Avoid updating the same timestamp more than once (if that's happening)
2. Update timestamps in one go at the end of the transaction (perhaps by
loading updates into a temp table).
3. Split the transaction in smaller chunks of activity.

> So.... there isn't much table size changing, but there are a lot of
> updates. Based on pg_stat_user_tables I suspect that the procedure is
> using indexes more than table scans. Is there a better way to know?

Not really. You can check the plans of queries within the function, but
there's no way to capture query plans of running functions.

--
Richard Huxton
Archonet Ltd