Feedback-ID: i76984098:Fastmail
Message-ID: <19c6bc0a-a560-4889-8902-093328fe41c5@aklaver.com>
Date: Wed, 21 Jan 2026 09:20:02 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pgBadger and postgres_fdw
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Colin 't Hart <colinthart@gmail.com>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAMon-aR8f3zi1Wytg-TCrbLa=sAooi4kTzbjukcvdt8G4mycbw@mail.gmail.com>
 <31af308b-1689-44e8-ab88-f6a72722b38b@aklaver.com>
 <CAMon-aT92+JQVTD54iwEmPpkFRDHDnhOzZGHMG6GoH_8Qh_EDg@mail.gmail.com>
 <1013bd5d-5356-497b-ae06-c02dc53caf92@aklaver.com>
Content-Language: en-US
In-Reply-To: <1013bd5d-5356-497b-ae06-c02dc53caf92@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/19c6bc0a-a560-4889-8902-093328fe41c5%40aklaver.com>
Precedence: bulk

On 1/21/26 08:59, Adrian Klaver wrote:
> On 1/21/26 08:12, Colin 't Hart wrote:
> 
>> 6. The 19 slowest queries in a 4 hour period are between 2 and 37 
>> minutes, with an average of over 10 minutes; they are all `fetch 100 
>> from c2`.
>>
>> The slowness itself isn't my question here; it was caused by having 
>> too few cores in the new environment, while the application was still 
>> assuming the higher core count and generating too many concurrent 
>> processes.
>>
>> My question is how to identify which connections / queries from 
>> postgres_fdw are generating the `fetch 100 from c2` queries, which, in 
>> turn, may quite possibly lead to a feature request for having these 
>> named uniquely.
> 
> My guess not.
> 
> See:
> 
> https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/ 
> postgres_fdw.c
> 
> Starting at line ~5212
> 
> fetch_size = 100;
> 
> and ending at line ~5234
> 
> /* Construct command to fetch rows from remote. */
>      snprintf(fetch_sql, sizeof(fetch_sql), "FETCH %d FROM c%u",
>               fetch_size, cursor_number);
> 
> So c2 is a cursor number.

If I am following this something postgres_fdw does to fetch the result 
in batches, so all the queries will have them.

FYI, the  fetch_size can be changed, see here:

https://www.postgresql.org/docs/17/postgres-fdw.html#POSTGRES-FDW-CONFIGURATION-PARAMETERS

F.36.1.4. Remote Execution Options


If you want connection/query information I would enable from here:

https://www.postgresql.org/docs/17/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

log_connections

log_disconnections

And at least temporarily:

log_statement = 'all'

The above will generate a lot of logs so you don't want to keep set for 
too long.


> 
>>
>> Thanks,
>>
>> Colin
>>
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com