Re: Database slowness -- my design, hardware, or both?

> LOG: statement: UPDATE Transactions
> SET previous_value = previous_value(id)
> WHERE new_value IS NOT NULL
> AND new_value <> ''
> AND node_id IN (SELECT node_id FROM NodeSegments)
> LOG: duration: 16687993.067 ms

I hope that I can presume some suggestions that I gleened after finishing my celko book. I don't
know if the suggestions presented will help in your case.

From the reading WHERE conditions such as <> '' or IS NOT NULL can be preformance killers as these
may discourge the optimizer from using an index scan. The suggest was to replace this with:

new_value > '<some minimum value possible in this field i.e. A>'

this WHERE conditions should only find non-NULL and non-empty strings.

Also, the IN is also know as a killer so the suggestion was to reform the query like so:

UPDATE Transactions
SET previous_value = previous_value(id)
FROM NodeSegments
WHERE Transactions.Node_id = NodeSegments.Node_id
AND Transactions.new_value > 'A'; --assuming A is a minimum value

I hope this can help.

Regards,
Richard Broersma Jr.