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

"Reuven M. Lerner" <reuven@xxxxxxxxxxxx> · Wed, 07 Mar 2007 00:12:20 -0600

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