Re: Slow update SQL - Mailing list pgsql-sql

On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote: <blockquote type="CITE"><pre>
<font color="#000000">[Please copy the mailing list on replies.]</font>

<font color="#000000">On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:</font>
<font color="#000000">> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:</font>
<font color="#000000">> > How many rows does the condition match?</font>
<font color="#000000">></font>
<font color="#000000">> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE</font>
<font color="#000000">> date_part('year',dxdate) > '2000';</font>
<font color="#000000">>  count</font>
<font color="#000000">> --------</font>
<font color="#000000">>  199209</font>
<font color="#000000">> (1 row)</font>

<font color="#000000">You're updating about a third of the table; an expression index on</font>
<font color="#000000">date_part probably wouldn't help because the planner is likely to</font>
<font color="#000000">stick with a sequential scan for such a large update.  Even if it</font>
<font color="#000000">did help it's likely to be a small fraction of the total time.</font>

<font color="#000000">The table definition you sent me showed nine indexes.  You might</font>
<font color="#000000">see a substantial performance improvement by dropping all the</font>
<font color="#000000">indexes, doing the update, then creating the indexes again (don't</font>
<font color="#000000">forget to vacuum and analyze the table after the update).  However,</font>
<font color="#000000">dropping the indexes has obvious implications for other queries so</font>
<font color="#000000">you might need to do the update at a time when that doesn't matter.</font>

<font color="#000000">> > Have you queried pg_locks</font>
<font color="#000000">> > to see if the update is blocked on an ungranted lock?</font>
<font color="#000000">> </font>
<font color="#000000">> I don't know what that is. How do I query pg_locks?</font>

<font color="#000000">SELECT * FROM pg_locks;</font>

<font color="#000000"><a
href="http://www.postgresql.org/docs/7.4/static/monitoring-locks.html">http://www.postgresql.org/docs/7.4/static/monitoring-locks.html</a></font>

<font color="#000000">> > What version of PostgreSQL are you running?</font>
<font color="#000000">> </font>
<font color="#000000">> 7.4.8. Thank you for your help.</font>

<font color="#000000">Newer versions generally perform better; consider upgrading to 8.0</font>
<font color="#000000">or 8.1 if possible.</font>

</pre></blockquote> Removing the indexes, running the update SQL, and then adding back the indexes worked much faster.
Thankyou for you help.