Databases
StackOverflow2013
Postsdbo
POSlow query after big INSERT in Postgres
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSlow query after big INSERT in Postgres
    text
    copied!<p>We are using Postgres 9.2 in RedHat. We have a table similar to the following:</p> <pre><code>CREATE TABLE BULK_WI ( BULK_ID INTEGER NOT NULL, USER_ID VARCHAR(20) NOT NULL, CHUNK_ID INTEGER, STATE VARCHAR(16), CONSTRAINT BASE_BULK_WI_PK PRIMARY KEY(BULK_ID,USER_ID) ); CREATE INDEX BASE_BULK_WI_IDX01 ON BULK_WI(STATE, CHUNK_ID); </code></pre> <p>As part of a batch job, we first add a number of rows to the table using a new BULK_ID. All new records have CHUNK_ID = NULL, STATE = 'PENDING'. The insert is between 500K and 1.5M rows. The size of the table when this happens is over 15M records.</p> <p>After the insert, we start processing the table in chunks. To do this, we first select a number of items for the next chunk, then process them. Selecting the items happens with the following query:</p> <pre><code>UPDATE BASE_BULK_WI wi SET wi.STATE = 'PROCESSING', wi.CHUNK_ID = $1 WHERE wi.STATE='PENDING' AND wi.BULK_ID = $2 AND wi.USER_ID IN (SELECT USER_ID FROM BASE_BULK_WI WHERE BULK_ID = $3 AND CHUNK_ID IS NULL AND STATE='PENDING' LIMIT $4 FOR UPDATE) </code></pre> <p><strong>$1</strong> increases with each chunk iteration, <strong>$2</strong> and <strong>$3</strong> are always the same (the BULK_ID just inserted), <strong>$4</strong> is usually between 2,000 and 10,000.</p> <p>The problem is that the first few chunks take a long time to be updated. For example, for a limit of 2000 most of the updates occur in under 1 second, while the first few ones take over 2 minutes.</p> <p>We are trying to understand why this happens and how to fix it. After reading the documentation:</p> <blockquote> <p>To ensure data page consistency, the first modification of a data page after each checkpoint results in logging the entire page content.</p> </blockquote> <p>we believe it's related to the checkpoints and the WAL, but we've been unable to pin it down. </p> <p>Any suggestions?</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