>>> On Fri, Nov 17, 2006 at 5:30 AM, in message
<[EMAIL PROTECTED]>, Hannu Krosing
<[EMAIL PROTECTED]> wrote:
> Ühel kenal päeval, E, 2006-11-13 kell 13:42, kirjutas Csaba Nagy:
>> > IMHO *most* UPDATEs occur on non-indexed fields. [snip]
>> > If my assumption is badly wrong on that then perhaps HOT would not be
>> > useful after all. If we find that the majority of UPDATEs meet the HOT
>> > pre-conditions, then I would continue to advocate it.
>> Just to confirm that the scenario is valid: our application has almost
>> all it's updates affecting only non-indexed columns. There are a few
>> exceptions, but the vast majority is non-indexed, and that holds to the
>> execution frequency too, not just for the count of tables/queries.
> One interesting case which should also be considered is conditional
> create index on payments(payment_id) where status = 'waiting';
> here the payment_id is not changed when processing the payment, but when
> status is changed to 'processed' it still should be removed from the
> How would this interact with HOT ?
I would say that at least 80% of our updates (probably higher) do not modify
indexed columns. We have a few very small tables (under 100 rows) which have
high update rates (often exceeding 100 updates per second) which are not
against indexed columns. These quickly degraded our performance until we set
pretty aggressive autovacuum parameters (20% + 1 row every 10 seconds) and
added a daily cluster to our maintenance crontab runs.
At the other extreme, we have a table which tracks the last modification
timestamp of each court case, indexed by timestamp, to support our SOAP
subscribers who want to stay up-to-date on all active court cases. Updates in
this table are both high volume and always involve an indexed column.
Like Hannu, we do use conditional indexes with high updates on columns in the
WHERE clause, although these columns are not part of the index sequence. For
example, we have a receivables table which contains a balance due. For audit
trail purposes these rows remain for many years after the balance hits zero,
but they're not something you want to look at when someone is standing at the
counter with their checkbook. We index by name where the balance is non-zero.
The balance is updated frequently, with most eventually hitting zero. (The
reason for the frequent updates is that the receivable is maintained by
triggers from the supporting assessment detail, so a receivable will be
initially added with a zero balance and may immediately be updated dozens of
times as the assessment detail is added.) Infrequently, the balance may hit
zero and subsequently become non-zero again.
I hope this is helpful.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings