Subject: Re: Asynchronous Append on postgres_fdw nodes.
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 Justin Pryzby <pryzby@telsasoft.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CAPmGK14igABfEruPPh1X=9KnjY=LquRHQij48oATSo1vWr7yOg@mail.gmail.com>
 <CAPmGK15xtFk2gb9-2b24mFtYBVWoGkmCPFPkM9RHpKVXGLk=sw@mail.gmail.com>
 <CAPmGK178d9-+aFgYorOY4n0pZmZ55WpQ7i1JcCagovYzAh+ueg@mail.gmail.com>
 <20210331.101137.148208859959175708.horikyota.ntt@gmail.com>
 <CAPmGK17T9Swy2FHZk4AVVKNzRxVnd96PX-18DSC2jeDXWNiBoQ@mail.gmail.com>
 <CAPmGK1637W30Wx3MnrReewhafn6F_0J76mrJGoFXFnpPq4QfvA@mail.gmail.com>
 <CAPmGK17O6fMfX84wWfypLWAO4NvD4fgDhm--oyzZ97YP+TxckA@mail.gmail.com>
 <2eb662bb-105d-fc20-7412-2f027cc3ca72@postgrespro.ru>
 <CAPmGK146cxSJyfM4Xv2jGTsF0ghmPEtwA7dS5_4cKo1oF6+ipw@mail.gmail.com>
 <CAPmGK17yjZg2ajhs3erpfOKj+sg6yZU_gwvaeOV+j8g2E2bdcA@mail.gmail.com>
 <a38bb206-8340-9528-5ef6-37de2d5cb1a3@postgrespro.ru>
 <CAPmGK16R=U2ok=H_QnibLS297_vmt68b6bJ8OYJzQ0DxMo+gyw@mail.gmail.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <2080193d-da8d-4132-f48c-8e29be8fcffc@postgrespro.ru>
Date: Tue, 11 May 2021 07:58:10 +0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0)
 Gecko/20100101 Thunderbird/78.10.1
MIME-Version: 1.0
In-Reply-To: 
 <CAPmGK16R=U2ok=H_QnibLS297_vmt68b6bJ8OYJzQ0DxMo+gyw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/2080193d-da8d-4132-f48c-8e29be8fcffc%40postgrespro.ru>
Precedence: bulk

On 10/5/21 08:03, Etsuro Fujita wrote:
> On Fri, May 7, 2021 at 7:32 PM Andrey Lepikhov
> I think a simple fix for this would be just remove the check whether
> the instr->running flag is set or not in InstrUpdateTupleCount().
> Attached is an updated patch, in which I also updated a comment in
> execnodes.h and docs in fdwhandler.sgml to match the code in
> nodeAppend.c, and fixed typos in comments in nodeAppend.c.
Your patch fixes the problem. But I found two more problems:

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 
 
 
SELECT * FROM ( 
 
 
               (SELECT * FROM f1) 
 
 
                              UNION ALL 
 
 
                                     (SELECT * FROM f2) 
 
 
                                                    UNION ALL 
 
 
                                                           (SELECT * 
FROM l3) 
 
                                                                    ) q1 
LIMIT 6709;
                           QUERY PLAN
--------------------------------------------------------------
  Limit (actual rows=6709 loops=1)
    ->  Append (actual rows=6709 loops=1)
          ->  Async Foreign Scan on f1 (actual rows=1 loops=1)
          ->  Async Foreign Scan on f2 (actual rows=1 loops=1)
          ->  Seq Scan on l3 (actual rows=6708 loops=1)

Here we scan 6710 tuples at low level but appended only 6709. Where did 
we lose one tuple?

2.
SELECT * FROM (
	(SELECT * FROM f1) 
 
 
                UNION ALL
	(SELECT * FROM f2) 
 
 
                UNION ALL
	(SELECT * FROM f3 WHERE a > 0)
) q1 LIMIT 3000;
                           QUERY PLAN
--------------------------------------------------------------
  Limit (actual rows=3000 loops=1)
    ->  Append (actual rows=3000 loops=1)
          ->  Async Foreign Scan on f1 (actual rows=0 loops=1)
          ->  Async Foreign Scan on f2 (actual rows=0 loops=1)
          ->  Foreign Scan on f3 (actual rows=3000 loops=1)

Here we give preference to the synchronous scan. Why?

-- 
regards,
Andrey Lepikhov
Postgres Professional