StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am by no means a DBA, but a database design where you'd frequently have to update 35 million rows might have… issues.</p> <p>A simple <code>WHERE status IS NOT NULL</code> might speed up things quite a bit (provided you have an index on status) – not knowing the actual use case, I'm assuming if this is run frequently, a great part of the 35 million rows might already have a null status.</p> <p>However, you can make loops within the query via the <a href="http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS" rel="nofollow noreferrer">LOOP statement</a>. I'll just cook up a small example:</p> <pre><code>CREATE OR REPLACE FUNCTION nullstatus(count INTEGER) RETURNS integer AS $$ DECLARE i INTEGER := 0; BEGIN FOR i IN 0..(count/1000 + 1) LOOP UPDATE orders SET status = null WHERE (order_id > (i*1000) and order_id <((i+1)*1000)); RAISE NOTICE 'Count: % and i: %', count,i; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; </code></pre> <p>It can then be run by doing something akin to:</p> <pre><code>SELECT nullstatus(35000000); </code></pre> <p>You might want to select the row count, but beware that the exact row count can take a lot of time. The PostgreSQL wiki has an article about <a href="http://wiki.postgresql.org/wiki/Slow_Counting" rel="nofollow noreferrer">slow counting and how to avoid it</a>.</p> <p>Also, the RAISE NOTICE part is just there to keep track on how far along the script is. If you're not monitoring the notices, or do not care, it would be better to leave it out.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload