public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Ibrar Ahmed <[email protected]>
To: Pavel Borisov <[email protected]>
To: Teodor Sigaev <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Dmitry Dolgov <[email protected]>
Cc: Gavin Flower <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: POC: GROUP BY optimization
Date: Wed, 21 Jul 2021 01:08:40 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
<CALtqXTe4rKXHXxNNGVy0wHVwGYYJpy2QNPTN29qq+ErBJJakrA@mail.gmail.com>
<[email protected]>
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
view thread (89+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC: GROUP BY optimization
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox