Re: Asynchronous Append on postgres_fdw nodes.

Hello, Andrey.

At Tue, 9 Jun 2020 14:20:42 +0500, Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> wrote in
> On 6/4/20 11:00 AM, Kyotaro Horiguchi wrote:
> > Removed a useless variable PgFdwScanState.result_ready.
> > Removed duplicate code from remove_async_node() by using
> > move_to_next_waiter().
> > Done some minor cleanups.
> >
> I am reviewing your code.
> A couple of variables are no longer needed (see changes.patch in
> attachment.

Thanks! The recent changes made them useless. Fixed.

> Something about the cost of an asynchronous plan:
>
> At the simple query plan (see below) I see:
> 1. Startup cost of local SeqScan is equal 0, ForeignScan - 100. But
> startup cost of Append is 0.

The result itself is right that the append doesn't wait for foreign
scans for the first iteration then fetches a tuple from the local
table. But the estimation is made just by an accident. If you
defined a foreign table as the first partition, the cost of Append
would be 100, which is rather wrong.

> 2. Total cost of an Append node is a sum of the subplans. Maybe in the
> case of asynchronous append we need to use some reduce factor?

Yes. For the reason mentioned above, foreign subpaths don't affect
the startup cost of Append as far as any sync subpaths exist. If no
sync subpaths exist, the Append's startup cost is the minimum startup
cost among the async subpaths.

I fixed cost_append so that it calculates the correct startup
cost. Now the function estimates as follows.

Append (Foreign(100), Foreign(100), Local(0)) => 0;
Append (Local(0), Foreign(100), Foreign(100)) => 0;
Append (Foreign(100), Foreign(100)) => 100;

> explain select * from parts;
>
> With Async Append:
> =====================
>
> Append (cost=0.00..2510.30 rows=106780 width=8)
> Async subplans: 3
> -> Async Foreign Scan on part_1 parts_2 (cost=100.00..177.80 rows=2260
> -> width=8)
> -> Async Foreign Scan on part_2 parts_3 (cost=100.00..177.80 rows=2260
> -> width=8)
> -> Async Foreign Scan on part_3 parts_4 (cost=100.00..177.80 rows=2260
> -> width=8)
> -> Seq Scan on part_0 parts_1 (cost=0.00..1443.00 rows=100000 width=8)

The SeqScan seems to be the first partition for the parent. It is the
first subnode at cost estimation. The result is right but it comes
from a wrong logic.

> Without Async Append:
> =====================
>
> Append (cost=0.00..2510.30 rows=106780 width=8)
> -> Seq Scan on part_0 parts_1 (cost=0.00..1443.00 rows=100000 width=8)
> -> Foreign Scan on part_1 parts_2 (cost=100.00..177.80 rows=2260 width=8)
> -> Foreign Scan on part_2 parts_3 (cost=100.00..177.80 rows=2260 width=8)
> -> Foreign Scan on part_3 parts_4 (cost=100.00..177.80 rows=2260 width=8)

The starup cost of the Append is the cost of the first subnode, that is, 0.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center