StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h3><a href="http://www.postgresql.org/docs/current/interactive/sql-analyze.html" rel="nofollow"><code>ANALYZE</code></a></h3> <p>The <a href="http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#AUTOVACUUM" rel="nofollow">autovacuum daemon</a> also runs <code>ANALYZE</code> automatically, but it takes some time to kick in. If you run <code>UPDATE</code> immediately after a huge <code>INSERT</code>, make sure to run <code>ANALYZE</code> <em>in between</em> to update statistics, or the query planner may make bad choices.</p> <h3><code>FROM</code> clause instead of <code>IN</code></h3> <p><code>IN</code> is notoriously slow with large subqueries. This might perform better:</p> <pre><code>UPDATE base_bulk_wi wi SET wi.state = 'PROCESSING' , wi.chunk_id = $1 FROM ( SELECT user_id, bulk_id FROM base_bulk_wi WHERE bulk_id = $3 AND chunk_id IS NULL AND state = 'PENDING' LIMIT $4 FOR UPDATE ) x WHERE wi.bulk_id = x.bulk_id AND wi.user_id = x.user_id; </code></pre> <h3>Indexes</h3> <p>A <a href="http://www.postgresql.org/docs/current/interactive/indexes-partial.html" rel="nofollow">partial index</a> like this should be the optimum for your case:</p> <pre><code>CREATE INDEX base_bulk_wi_partial_idx01 ON bulk_wi(chunk_id) WHERE state = 'PENDING' AND chunk_id IS NULL; </code></pre> <p>For best performance, create this index <em>after</em> your <code>INSERT</code>. If it already exists, it might help to drop before and recreate after.</p> <p>One might think it's a good idea to include <code>bulk_id</code> in this index to allow for an <strong>index-only scan</strong> in Postgres 9.2. But since you have <code>FOR UPDATE</code> in the subquery, this is not an option anyway. </p> <p>It would help quite a bit, if <code>user_id</code> was an <code>integer</code> instead of <code>varchar</code>. (Foreign key to a user table.) Besides faster handling and a smaller table, two integer fit perfectly in a minimum-size index. Your primary key would benefit quite a bit.</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