> 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
