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
