Subject: Re: printing results of query to file in different times
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
References: <634f2eb6-daf6-6314-e6f5-1b531b50a9ec@comcast.net>
 <CA+t6e1kS59uQ7HYSrx-KH2ieFLhJUsnprJ1rGBNPvO1n0YjvbA@mail.gmail.com>
 <62fce205-db63-ed25-c726-d8c442f6223f@comcast.net>
 <CA+t6e1kbSHK4qWmHY3nD3L6rhGvhY5gx3XAKEJwfoWoKm6_GLw@mail.gmail.com>
Cc: pgsql-performance@postgresql.org
From: George Neuner <gneuner2@comcast.net>
Message-ID: <ccea0e2f-641b-ac78-5b0a-b1eb7f8b8a4e@comcast.net>
Date: Wed, 6 Sep 2017 12:55:32 -0400
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1kbSHK4qWmHY3nD3L6rhGvhY5gx3XAKEJwfoWoKm6_GLw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------7B1DA5CFBDAFB0122E64207D"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------7B1DA5CFBDAFB0122E64207D
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Hi Mariel,

On 9/6/2017 4:38 AM, Mariel Cherkassky wrote:
> I'm sure that those tables arent involved in any other transaction 
> when the loop is running. Anything else that I can check ? I think 
> that mybe its connected to some fetching properties but Im not 
> familiar with what settings..

That's the problem.  There are a lot of things that can affect query 
performance, but most of them _won't_ affect an open cursor unless 
isolation is low and the query's source tables are changing due to 
ongoing operations.  Each time the cursor is accessed, the query's 
source tables are checked for modifications, and if they have been 
changed, the cursor's query is re-executed ... potentially changing the 
result set.

Not that it matters here, but you didn't show your actual query. Even if 
you are only fetching 100 rows, the query may be doing a lot of work 
(joins, sorts, etc.) to identify those rows.  If a complicated query is 
being executed over and over due to ongoing table modifications ...    
That's why I suggested using a temporary table that you know won't be 
modified while the cursor is open on it - it's a way of side-stepping 
isolation issues that are beyond your control.

If there really is no contention for the source tables, the only other 
possibilities are a badly over-loaded (or mis-configured) server, a 
problem with the storage system (e.g., a bad disk that is causing 
hiccups rather than outright failures), or some unknown issue with the 
extension you are using.

I'm afraid I'm out of suggestions.
George

--------------7B1DA5CFBDAFB0122E64207D
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    Hi Mariel,<br>
    <br>
    <div class="moz-cite-prefix">On 9/6/2017 4:38 AM, Mariel Cherkassky
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+t6e1kbSHK4qWmHY3nD3L6rhGvhY5gx3XAKEJwfoWoKm6_GLw@mail.gmail.com">
      <div dir="rtl">
        <div dir="ltr">I'm sure that those tables arent involved in any
          other transaction when the loop is running. Anything else that
          I can check ? I think that mybe its connected to some fetching
          properties but Im not familiar with what settings..</div>
      </div>
    </blockquote>
    <br>
    That's the problem.  There are a lot of things that can affect query
    performance, but most of them <u>won't</u> affect an open cursor
    unless isolation is low and the query's source tables are changing
    due to ongoing operations.  Each time the cursor is accessed, the
    query's source tables are checked for modifications, and if they
    have been changed, the cursor's query is re-executed ... potentially
    changing the result set.<br>
    <br>
    Not that it matters here, but you didn't show your actual query. 
    Even if you are only fetching 100 rows, the query may be doing a lot
    of work (joins, sorts, etc.) to identify those rows.  If a
    complicated query is being executed over and over due to ongoing
    table modifications ...    That's why I suggested using a temporary
    table that you know won't be modified while the cursor is open on it
    - it's a way of side-stepping isolation issues that are beyond your
    control.<br>
    <br>
    If there really is no contention for the source tables, the only
    other possibilities are a badly over-loaded (or mis-configured)
    server, a problem with the storage system (e.g., a bad disk that is
    causing hiccups rather than outright failures), or some unknown
    issue with the extension you are using.<br>
    <br>
    I'm afraid I'm out of suggestions.<br>
    George<br>
  </body>
</html>

--------------7B1DA5CFBDAFB0122E64207D--