Hi, Alvaro Herrera. You wrote:
Don't assume -- measure. I had a query which ran orders of magnitude
faster because I interpolated the constant list in the big query. The
table from which the interpolated values were being extracted had about
30 rows or so.
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.
Reuven
