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

>  OK, I modified things to use interpolation.  Here's the updated query:
>
>
> explain  UPDATE Transactions
>                 SET previous_value = previous_value(id)
>               WHERE new_value IS NOT NULL
>                 AND new_value <> ''
>                 AND node_id IN (351, 169, 664, 240);
>
>  And here is the query plan produced by explain:
>
>                                     QUERY
> PLAN
> ----------------------------------------------------------------------------------
>   Bitmap Heap Scan on transactions  (cost=8842.88..98283.93 rows=407288
> width=249)
>     Recheck Cond: (node_id = ANY
> ('{351,169,664,240}'::integer[]))
>     Filter: ((new_value IS NOT NULL) AND (new_value <> ''::text))
>     ->  Bitmap Index Scan on node_id_idx  (cost=0.00..8842.88 rows=434276
> width=0)
>           Index Cond: (node_id = ANY
> ('{351,169,664,240}'::integer[]))
>  (5 rows)
>  I'm still a bit surprised by how different the query plan came out with
> what would seem like a minor change.

Do you have new \timings?

What you or I think is a minor change isn't necessarily what the
planner thinks is a minor change, especially when you change data from
something that requires a query to something that is determinate.  I
would suggest changing your function to remove as many such queries as
possible too (I am thinking of the order by limit 1).  This would be a
good move also in that you narrow down the amount of moving parts to
diagnose and it just makes the whole thing cleaner.

The meta-moral is that db optimization requires systematic
experimentation.  Use the database to store the results of the various
experiments!

In light of this, I would suggest you try removing the check clauses
and seeing if you get a difference too.  Just like Francis Bacon said
-- don't deduce from first principles, experiment!

I would also try amortizing the analysis with triggers, rather than
building the table all at once; this may be better or worse, depending
on the on-line character of the application (e.g., if they are waiting
at an ATM, in might be a deal breaker to add two seconds to do an
insert / update, but not if you are tailing off a log file that gets
updated every minute or so.)

W