Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uH2i2-00CP04-Mh for pgsql-bugs@arkaria.postgresql.org; Mon, 19 May 2025 15:47:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uH2i1-00GgBb-Np for pgsql-bugs@arkaria.postgresql.org; Mon, 19 May 2025 15:47:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uH2i1-00GgAn-1V for pgsql-bugs@lists.postgresql.org; Mon, 19 May 2025 15:47:21 +0000 Received: from mail.postgrespro.ru ([93.174.132.70]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uH2hv-002b2u-1p for pgsql-bugs@lists.postgresql.org; Mon, 19 May 2025 15:47:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1747669632; bh=LLcR3PuL4obYOVue9QiZ8nXZz8xR09fMDmfxOv1Bmy8=; h=Message-ID:Date:User-Agent:Subject:To:References:From:In-Reply-To: From; b=zyT3eN2Ejk2ZX9xsElvbQgkO2+vIR4SZVf1xhLdrlV0/AfIrfBxNQ11JdoB4zWuBY GjLDkQeTVZQDkdq9Qs/y9WGf3KTtRpcFKQE5wWoHyXtQYeJCxZDgTikk7P0zMMk1mj q8x9gQuBrnkduNx2Dnq7v3rfdhis5SMH1d8LDwL5HrGJ8gsWAWK6PjJdoHh+5/DnfT oYOh5X558K/3J1MdAmoV8GxhK+SHAfxgK5MKVcsYrQgcOirLsI3j5Y5Xrv2g+vNkG7 rUFNwNibY+dAtzJheSEp1tKsQfVJjvOeNByna4IyMB14owKQ3DjZEC1cxNljuQv+l2 bvUr0aNHKKyKg== Received: from [172.30.48.78] (unknown [93.174.131.138]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: a.rybakina@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 92DD160511; Mon, 19 May 2025 18:47:11 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------tytLhgC6Di8aIgJGZPB90Aq0" Message-ID: <56794c53-1b33-49e8-b9c0-d37a2329ce3b@postgrespro.ru> Date: Mon, 19 May 2025 11:47:05 -0400 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: BUG #18935: The optimiser's choice of sort doubles the execution time. To: xiaohongjun@stu.xidian.edu.cn, pgsql-bugs@lists.postgresql.org, PG Bug reporting form References: <18935-afc77176f41fbecb@postgresql.org> Content-Language: en-US From: Alena Rybakina In-Reply-To: <18935-afc77176f41fbecb@postgresql.org> X-KSMG-AntiPhishing: NotDetected, bases: 2025/05/19 14:06:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2025/05/19 10:16:00 #27991851 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------tytLhgC6Di8aIgJGZPB90Aq0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi! On 19.05.2025 08:42, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 18935 > Logged by: hongjun xiao > Email address:xiaohongjun@stu.xidian.edu.cn > PostgreSQL version: 17.4 > Operating system: Ubuntu 20.04.6 LTS > Description: > > database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON > ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0, > t0.c0))))))) GROUP BY t0.c0; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > Group (cost=365.27..365.62 rows=70 width=13) (actual time=16.143..17.958 > rows=121 loops=1) > Group Key: t0.c0 > -> Sort (cost=365.27..365.45 rows=70 width=13) (actual > time=16.141..16.540 rows=12688 loops=1) > Sort Key: t0.c0 > Sort Method: quicksort Memory: 385kB > -> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual > time=0.055..8.431 rows=12688 loops=1) > Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * > '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0)))) > Rows Removed by Join Filter: 1650 > -> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) > (actual time=0.025..0.040 rows=134 loops=1) > -> Materialize (cost=0.00..2.61 rows=107 width=13) (actual > time=0.000..0.005 rows=107 loops=134) > -> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) > (actual time=0.015..0.028 rows=107 loops=1) > Planning Time: 0.105 ms > Execution Time: 17.998 ms > (13 rows) > database4=# set enable_sort=false; > SET > database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON > ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0, > t0.c0))))))) GROUP BY t0.c0; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=363.30..364.00 rows=70 width=13) (actual > time=8.034..8.042 rows=121 loops=1) > Group Key: t0.c0 > Batches: 1 Memory Usage: 40kB > -> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual > time=0.019..5.680 rows=12688 loops=1) > Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * > '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0)))) > Rows Removed by Join Filter: 1650 > -> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual > time=0.010..0.033 rows=134 loops=1) > -> Materialize (cost=0.00..2.61 rows=107 width=13) (actual > time=0.000..0.003 rows=107 loops=134) > -> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) > (actual time=0.004..0.007 rows=107 loops=1) > Planning Time: 0.060 ms > Execution Time: 8.064 ms > (11 rows) I noticed underestimation problem here because of inability to predict the actual number of rows for this expression by the planner: *range_merge(t1.c0, t0.c0))* As I know it is not possible to create extended statistics on different relations. |CREATE STATISTICS t0_t1_stats (dependencies) ON c0 FROM t0, t1;| ERROR: only a single relation is allowed in CREATE STATISTICS But I added index on table t0: create index idx on t0(c0) and sort operation was skipped because of index scan but I could got this only with disable seqscan: set enable_seqscan = off;  Group  (cost=0.14..375.12 rows=70 width=13) (actual time=0.129..44.850 rows=121.00 loops=1)    Group Key: t0.c0    Buffers: shared hit=3    ->  Nested Loop  (cost=0.14..374.94 rows=70 width=13) (actual time=0.125..37.008 rows=12688.00 loops=1)          Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))          Rows Removed by Join Filter: 1650          Buffers: shared hit=3          ->  Index Only Scan using idx on t0  (cost=0.14..14.15 rows=134 width=13) (actual time=0.067..0.267 rows=134.00 loops=1)                Heap Fetches: 134                Index Searches: 1                Buffers: shared hit=2          ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.016 rows=107.00 loops=134)                Storage: Memory  Maximum Storage: 21kB                Buffers: shared hit=1                ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.026..0.063 rows=107.00 loops=1)                      Disabled: true                      Buffers: shared hit=1  Planning Time: 0.289 ms  Execution Time: 44.956 ms (19 rows) The execution ti,e on my laptop is almost the same with disabled sorting: set enable_sort = off; -----------------------------------------------------------------------------------------------------------------------  HashAggregate  (cost=363.30..364.00 rows=70 width=13) (actual time=55.001..55.077 rows=121.00 loops=1)    Group Key: t0.c0    Batches: 1  Memory Usage: 40kB    Buffers: shared hit=2    ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual time=0.050..38.315 rows=12688.00 loops=1)          Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))          Rows Removed by Join Filter: 1650          Buffers: shared hit=2          ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13) (actual time=0.024..0.065 rows=134.00 loops=1)                Buffers: shared hit=1          ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.014 rows=107.00 loops=134)                Storage: Memory  Maximum Storage: 21kB                Buffers: shared hit=1                ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.007..0.023 rows=107.00 loops=1)                      Buffers: shared hit=1  Planning Time: 0.144 ms  Execution Time: 55.166 ms (17 rows) Original query plan: -----------------------------------------------------------------------------------------------------------------------------  Group  (cost=365.27..365.62 rows=70 width=13) (actual time=47.471..51.439 rows=121.00 loops=1)    Group Key: t0.c0    Buffers: shared hit=5    ->  Sort  (cost=365.27..365.45 rows=70 width=13) (actual time=47.467..48.234 rows=12688.00 loops=1)          Sort Key: t0.c0          Sort Method: quicksort  Memory: 385kB          Buffers: shared hit=5          ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual time=0.077..34.222 rows=12688.00 loops=1)                Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))                Rows Removed by Join Filter: 1650                Buffers: shared hit=2                ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13) (actual time=0.028..0.061 rows=134.00 loops=1)                      Buffers: shared hit=1                ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.015 rows=107.00 loops=134)                      Storage: Memory  Maximum Storage: 21kB                      Buffers: shared hit=1                      ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.016..0.053 rows=107.00 loops=1)                            Buffers: shared hit=1  Planning:    Buffers: shared hit=25 read=3  Planning Time: 0.863 ms  Execution Time: 51.609 ms -- Regards, Alena Rybakina Postgres Professional --------------tytLhgC6Di8aIgJGZPB90Aq0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Hi!
On 19.05.2025 08:42, PG Bug reporting form wrote:
The following bug has been logged on the website:

Bug reference:      18935
Logged by:          hongjun xiao
Email address:      xiaohongjun@stu.xidian.edu.cn
PostgreSQL version: 17.4
Operating system:   Ubuntu 20.04.6 LTS
Description:        

database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
t0.c0))))))) GROUP BY t0.c0;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Group  (cost=365.27..365.62 rows=70 width=13) (actual time=16.143..17.958
rows=121 loops=1)
   Group Key: t0.c0
   ->  Sort  (cost=365.27..365.45 rows=70 width=13) (actual
time=16.141..16.540 rows=12688 loops=1)
         Sort Key: t0.c0
         Sort Method: quicksort  Memory: 385kB
         ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual
time=0.055..8.431 rows=12688 loops=1)
               Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 *
'[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
               Rows Removed by Join Filter: 1650
               ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13)
(actual time=0.025..0.040 rows=134 loops=1)
               ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual
time=0.000..0.005 rows=107 loops=134)
                     ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13)
(actual time=0.015..0.028 rows=107 loops=1)
 Planning Time: 0.105 ms
 Execution Time: 17.998 ms
(13 rows)
database4=# set enable_sort=false;
SET
database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
t0.c0))))))) GROUP BY t0.c0;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=363.30..364.00 rows=70 width=13) (actual
time=8.034..8.042 rows=121 loops=1)
   Group Key: t0.c0
   Batches: 1  Memory Usage: 40kB
   ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual
time=0.019..5.680 rows=12688 loops=1)
         Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 *
'[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
         Rows Removed by Join Filter: 1650
         ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13) (actual
time=0.010..0.033 rows=134 loops=1)
         ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual
time=0.000..0.003 rows=107 loops=134)
               ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13)
(actual time=0.004..0.007 rows=107 loops=1)
 Planning Time: 0.060 ms
 Execution Time: 8.064 ms
(11 rows)

I noticed underestimation problem here because of inability to predict the actual number of rows for this expression by the planner: range_merge(t1.c0, t0.c0))

As I know it is not possible to create extended statistics on different relations.

CREATE STATISTICS t0_t1_stats (dependencies) ON c0 FROM t0, t1;
ERROR: only a single relation is allowed in CREATE STATISTICS


But I added index on table t0: create index idx on t0(c0) and sort operation was skipped because of index scan but I could got this only with disable seqscan:

set enable_seqscan = off;

 Group  (cost=0.14..375.12 rows=70 width=13) (actual time=0.129..44.850 rows=121.00 loops=1)
   Group Key: t0.c0
   Buffers: shared hit=3
   ->  Nested Loop  (cost=0.14..374.94 rows=70 width=13) (actual time=0.125..37.008 rows=12688.00 loops=1)
         Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
         Rows Removed by Join Filter: 1650
         Buffers: shared hit=3
         ->  Index Only Scan using idx on t0  (cost=0.14..14.15 rows=134 width=13) (actual time=0.067..0.267 rows=134.00 loops=1)
               Heap Fetches: 134
               Index Searches: 1
               Buffers: shared hit=2
         ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.016 rows=107.00 loops=134)
               Storage: Memory  Maximum Storage: 21kB
               Buffers: shared hit=1
               ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.026..0.063 rows=107.00 loops=1)
                     Disabled: true
                     Buffers: shared hit=1
 Planning Time: 0.289 ms
 Execution Time: 44.956 ms
(19 rows)


The execution ti,e on my laptop is almost the same with disabled sorting:

set enable_sort = off;

-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=363.30..364.00 rows=70 width=13) (actual time=55.001..55.077 rows=121.00 loops=1)
   Group Key: t0.c0
   Batches: 1  Memory Usage: 40kB
   Buffers: shared hit=2
   ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual time=0.050..38.315 rows=12688.00 loops=1)
         Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
         Rows Removed by Join Filter: 1650
         Buffers: shared hit=2
         ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13) (actual time=0.024..0.065 rows=134.00 loops=1)
               Buffers: shared hit=1
         ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.014 rows=107.00 loops=134)
               Storage: Memory  Maximum Storage: 21kB
               Buffers: shared hit=1
               ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.007..0.023 rows=107.00 loops=1)
                     Buffers: shared hit=1
 Planning Time: 0.144 ms
 Execution Time: 55.166 ms
(17 rows)

Original query plan:

-----------------------------------------------------------------------------------------------------------------------------
 Group  (cost=365.27..365.62 rows=70 width=13) (actual time=47.471..51.439 rows=121.00 loops=1)
   Group Key: t0.c0
   Buffers: shared hit=5
   ->  Sort  (cost=365.27..365.45 rows=70 width=13) (actual time=47.467..48.234 rows=12688.00 loops=1)
         Sort Key: t0.c0
         Sort Method: quicksort  Memory: 385kB
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.00..363.13 rows=70 width=13) (actual time=0.077..34.222 rows=12688.00 loops=1)
               Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
               Rows Removed by Join Filter: 1650
               Buffers: shared hit=2
               ->  Seq Scan on t0  (cost=0.00..2.34 rows=134 width=13) (actual time=0.028..0.061 rows=134.00 loops=1)
                     Buffers: shared hit=1
               ->  Materialize  (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.015 rows=107.00 loops=134)
                     Storage: Memory  Maximum Storage: 21kB
                     Buffers: shared hit=1
                     ->  Seq Scan on t1  (cost=0.00..2.07 rows=107 width=13) (actual time=0.016..0.053 rows=107.00 loops=1)
                           Buffers: shared hit=1
 Planning:
   Buffers: shared hit=25 read=3
 Planning Time: 0.863 ms
 Execution Time: 51.609 ms

-- 
Regards,
Alena Rybakina
Postgres Professional
--------------tytLhgC6Di8aIgJGZPB90Aq0--