Subject: Re: POC: GROUP BY optimization
To: Ibrar Ahmed <ibrar.ahmad@gmail.com>,
 Pavel Borisov <pashkin.elfe@gmail.com>, Teodor Sigaev <teodor@sigaev.ru>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Dmitry Dolgov <9erthalion6@gmail.com>,
 Gavin Flower <GavinFlower@archidevsys.co.nz>,
 Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
References: 
 <CA+q6zcVRrd-z4YZ4M43ccst7aGL9==w5r1fionRWhP9ot6mybQ@mail.gmail.com>
 <20190503215510.bcr5ycszntqg65tw@development>
 <CA+q6zcVjCVkAhoLD6rjDoPGdfWDjQLD=N2tvfH1yU+Eq+iby9w@mail.gmail.com>
 <20190524225725.embuha33qvc5avz3@development>
 <20200514235220.xewrrwjvatxzn3g6@development>
 <CALT9ZEFU60nW9TJqHUs9Ex_tqv__7t=jfF4Q=iwwxJTO0PY7Rg@mail.gmail.com>
 <20201026085721.g6h5xljxvodnmk34@localhost>
 <CALT9ZEF8FUZoijXnFsDXNgqbSHFzaCU2q0cmJFnZ4z2wNZO98w@mail.gmail.com>
 <20201026104040.6bigvej6f55vjvgp@localhost>
 <20201027201509.jfv6sfhrmxhdwlvk@development>
 <20201029145025.tn3tekboot37nkbf@localhost>
 <CALT9ZEEqnS+tObVjLWZrUme-PZQ-O0krpFCfQhwG-4ziSeSrBQ@mail.gmail.com>
 <CALtqXTdsuDq_dsbE0F0XHd8U2-ceSdvN9wr1zkap=sJypgBJBA@mail.gmail.com>
 <CALT9ZEFwfCQAzUVfUxak=fzhgwez3jn=5XcpsU9HbKQ_VjrZ-A@mail.gmail.com>
 <22c44f98-bfa8-8630-62b5-5155e11eb284@enterprisedb.com>
 <CALtqXTe4rKXHXxNNGVy0wHVwGYYJpy2QNPTN29qq+ErBJJakrA@mail.gmail.com>
 <b1d468ba-2961-044c-deab-817b9a900e9c@enterprisedb.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <ba2eccf2-d623-c12c-f7c2-a58ac4917c83@enterprisedb.com>
Date: Wed, 21 Jul 2021 01:08:40 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <b1d468ba-2961-044c-deab-817b9a900e9c@enterprisedb.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ba2eccf2-d623-c12c-f7c2-a58ac4917c83%40enterprisedb.com>
Precedence: bulk

On 7/20/21 3:12 PM, Tomas Vondra wrote:
> ...
>
> The other comments from the review still apply - I'm particularly
> concerned about the (1) point, i.e. plan changes in postgres_fdw. Those
> seem to be rather strange (LIMIT not being pushed down in queries
> without any grouping). I'd bet this is due to changes in sort costing
> and does not seem very desirable.
> 

I did look at this a bit closer, and yes - this very much seems like a
costing issue in the patch. The first query in postgres_fdw that changes
switches from a query with LIMIT pushed to remote

                              QUERY PLAN
------------------------------------------------------------------------
 Foreign Scan  (cost=196.29..196.52 rows=10 width=14)
   Output: t1.c1, t2.c1, t1.c3
   Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
   Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3 FROM ("S 1"."T 1" r1
               INNER JOIN "S 1"."T 1" r2 ON ... LIMIT ... OFFSET ...
(4 rows)

to the LIMIT executed locally

                              QUERY PLAN
-------------------------------------------------------------------------
 Limit  (cost=241.72..241.94 rows=10 width=14)
   Output: t1.c1, t2.c1, t1.c3
   ->  Foreign Scan  (cost=239.47..261.97 rows=1000 width=14)
         Output: t1.c1, t2.c1, t1.c3
         Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
         Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3 FROM ("S 1"."T 1"
         r1 INNER JOIN ...
(6 rows)

The FDW code runs explain on both queries - with and without LIMIT
pushed to remote side, to get estimates, and without the patch it gets this:

                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=106.97..109.47 rows=1000 width=14)
   Sort Key: r1.c3, r1."C 1"
   ->  Hash Join  (cost=33.50..57.14 rows=1000 width=14)
         Hash Cond: (r1."C 1" = r2."C 1")
         ->  Seq Scan on "T 1" r1  (cost=0.00..21.00 rows=1000 width=10)
         ->  Hash  (cost=21.00..21.00 rows=1000 width=4)
               ->  Seq Scan on "T 1" r2  (cost=0.00..21.00 rows=1000
width=4)
(7 rows)

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit  (cost=96.29..96.32 rows=10 width=14)
   ->  Sort  (cost=96.04..98.54 rows=1000 width=14)
         Sort Key: r1.c3, r1."C 1"
         ->  Hash Join  (cost=33.50..57.14 rows=1000 width=14)
               Hash Cond: (r1."C 1" = r2."C 1")
               ->  Seq Scan on "T 1" r1  (cost=0.00..21.00 rows=1000
width=10)
               ->  Hash  (cost=21.00..21.00 rows=1000 width=4)
                     ->  Seq Scan on "T 1" r2  (cost=0.00..21.00
rows=1000 width=4)
(8 rows)


while with the patch it gets this:

                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=139.47..141.97 rows=1000 width=14)
   Sort Key: r1.c3, r1."C 1"
   ->  Hash Join  (cost=33.50..57.14 rows=1000 width=14)
         Hash Cond: (r1."C 1" = r2."C 1")
         ->  Seq Scan on "T 1" r1  (cost=0.00..21.00 rows=1000 width=10)
         ->  Hash  (cost=21.00..21.00 rows=1000 width=4)
               ->  Seq Scan on "T 1" r2  (cost=0.00..21.00 rows=1000
width=4)
(7 rows)

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit  (cost=145.75..145.77 rows=10 width=14)
   ->  Sort  (cost=145.50..148.00 rows=1000 width=14)
         Sort Key: r1.c3, r1."C 1"
         ->  Hash Join  (cost=33.50..57.14 rows=1000 width=14)
               Hash Cond: (r1."C 1" = r2."C 1")
               ->  Seq Scan on "T 1" r1  (cost=0.00..21.00 rows=1000
width=10)
               ->  Hash  (cost=21.00..21.00 rows=1000 width=4)
                     ->  Seq Scan on "T 1" r2  (cost=0.00..21.00
rows=1000 width=4)
(8 rows)


Notice that the costs get "inverted"

                       master            patched
   ----------------------------------------------
    no limit   106.97..109.47     139.47..141.97
    limit       96.29.. 96.32     145.75..145.77

so the limit looks a bit more expensive - just enough so that it seems
cheaper to transfer more rows and execute the limit locally.

IMO this looks rather suspicious (or wrong), and compute_cpu_sort_cost
may need some fixes. I wonder why differs from the old code so much ...


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company