StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate query too slow on Postgres 9.1
text
Body
copied!<p>My problem is that I have a very slow update query on a table with 14 million rows. I tried different things to tune my server which brought good performance but not for update queries.</p> <p>I have two tables:</p> <ul> <li>T1 with 4 columns and 3 indexes on it (530 rows)</li> <li>T2 with 15 columns and 3 indexes on it (14 millions rows)</li> <li>I want to update the field vid (type integer) in T2 by the same value of vid in T1 by joining the two tables on a text field stxt.</li> </ul> <p>Here is my query and its output:</p> <pre><code>explain analyse update T2 set vid=T1.vid from T1 where stxt2 ~ stxt1 and T2.vid = 0; </code></pre> <pre> Update on T2 (cost=0.00..9037530.59 rows=2814247 width=131) (actual time=25141785.741..25141785.741 rows=0 loops=1) -> Nested Loop (cost=0.00..9037530.59 rows=2814247 width=131) (actual time=32.636..25035782.995 rows=679354 loops=1) Join Filter: ((T2.stxt2)::text ~ (T1.stxt1)::text) -> Seq Scan on T2 (cost=0.00..594772.96 rows=1061980 width=121) (actual time=0.067..5402.614 rows=1037809 loops=1) Filter: (vid= 1) -> Materialize (cost=0.00..17.95 rows=530 width=34) (actual time=0.000..0.069 rows=530 loops=1037809) -> Seq Scan on T1 (cost=0.00..15.30 rows=530 width=34) (actual time=0.019..0.397 rows=530 loops=1) Total runtime: 25141785.904 ms </pre> <p>As you can see the query took approximately 25141 seconds (~ 7 hours). f I understood well, the planner estimates the execution time to be 9037 seconds (~ 2.5 hours). Am I missing something here?</p> <p>Here are information about my server config:</p> <ul> <li>CentOS 5.8, 20GB of RAM</li> <li>shared_buffers = 12GB</li> <li>work_mem = 64MB</li> <li>maintenance_work_mem = 64MB</li> <li>bgwriter_lru_maxpages = 500</li> <li>checkpoint_segments = 64</li> <li>checkpoint_completion_target = 0.9</li> <li>effective_cache_size = 10GB</li> </ul> <p>I have run vacuum full and analyse several times on table T2 but this still does not improve much the situation.</p> <p>PS: if I set full_page_writes to off, this improves considerably update queries, but I don't want to risk data loss. Do you please have any recommandations?</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