On 03/07/2013 07:40 AM, Filip Rembiałkowski wrote:
Why do you assume that opening a cursor with ORDER BY is equivalent to fetching all rows? It is not.
It is when 90% of a table's 65M rows are part of the result set. Calculating the cursor puts the retrieved rows in a temporary space until the cursor is freed. Calculating that many rows with that order by, on our particular system took about 75 seconds. The problem wasn't really that it was fetching the rows, but that it was closing the cursor *right* afterwards. Like this: 2013-03-04 07:46:06 CST DEBUG1 remoteHelperThread_1_1: 78.010 seconds until close cursor We saw that in the slave log about every 80 seconds. When I set up slony on our staging system, it only closed the cursor once all 20M rows were fetched and deleted from the subscriber node. Clearly whatever was happening in our production environment wasn't normal. But at the time, it *looked* like slony was forcing it to calculate and fill the cursor, getting the top 500 rows, and closing it right away on purpose. Turns out, that was abnormal. :) So, I'm not shocked anymore. A one time cursor calculation is fine. Now I'm confused as to what broke it or why it was acting that way when that clearly isn't the design goal. Ah well. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
