Re: Database slowness -- my design, hardware, or both? - Mailing list pgsql-general

>     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.