Re: Running some query in low priority

On Thu, 5 Nov 2009, Grzegorz Jaśkiewicz wrote:

If it is an insert of some sort, than divide it up. If it is a query that runs over data,
use limits, and do it in small batches. Overall, divide in conquer approach works in
these scenarios.

Unfortunately, dividing the work up can cause a much greater load, which would make things worse. If you are inserting in smaller chunks and committing more frequently that can reduce performance. If you split up queries with limit and offset, that will just multiply the number of times the query has to be run. Each time, the query will be evaluated, the first <offset> rows thrown away, and the next <limit> rows returned, which will waste a huge amount of time.

If you are inserting data, then use a COPY from stdin, and then you can throttle the data stream. When you are querying, declare a cursor, and fetch from it at a throttled rate.