From: Tom Lane <tgl@sss.pgh.pa.us>
To: jinhui-lai@foxmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18973: The default enable_material=ON affects the cost
 estimation of optimizer, resulting in 10968x slow
In-reply-to: <18973-3bc2a3f826a72cce@postgresql.org>
References: <18973-3bc2a3f826a72cce@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 30 Jun 2025 13:44:19 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2826335.1751295418.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 30 Jun 2025 10:56:58 -0400
Message-ID: <2826336.1751295418@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2826336.1751295418%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I may have found a performance issue. The parameter enable_material is s=
et
> to ON by default, and it affects the cost estimation of optimizer, resul=
ting
> in 10968x slow. You can reproduce it as follows:
> CREATE TABLE t0(c0 INT8);
> CREATE TABLE t1(c1 INT8);
> INSERT INTO t1 SELECT i FROM generate_series(1, 100000000) AS i;
> SELECT * FROM t0 INNER JOIN t1 ON t0.c0 !=3D t1.c1;
>  c0 | c1
> ----+----
> (0 rows)
> Time: 9794.016 ms (00:09.794)

The problem with this example is that you didn't ANALYZE the tables.
If you do, it switches to a plan without Materialize:

regression=3D# CREATE TABLE t0(c0 INT8);
CREATE TABLE
regression=3D# CREATE TABLE t1(c1 INT8);
CREATE TABLE
regression=3D# INSERT INTO t1 SELECT i FROM generate_series(1, 100000000) =
AS i;
INSERT 0 100000000
regression=3D# explain analyze SELECT * FROM t0 INNER JOIN t1 ON t0.c0 !=3D=
 t1.c1;
                                                          QUERY PLAN      =
                                                     =

--------------------------------------------------------------------------=
-----------------------------------------------------
 Nested Loop  (cost=3D0.00..3391443465.73 rows=3D224870062964 width=3D16) =
(actual time=3D19992.481..19992.483 rows=3D0.00 loops=3D1)
   Join Filter: (t0.c0 <> t1.c1)
   Buffers: shared read=3D442478 dirtied=3D442478 written=3D428541
   ->  Seq Scan on t1  (cost=3D0.00..1442478.28 rows=3D100000028 width=3D8=
) (actual time=3D0.136..11957.262 rows=3D100000000.00 loops=3D1)
         Buffers: shared read=3D442478 dirtied=3D442478 written=3D428541
   ->  Materialize  (cost=3D0.00..43.90 rows=3D2260 width=3D8) (actual tim=
e=3D0.000..0.000 rows=3D0.00 loops=3D100000000)
         Storage: Memory  Maximum Storage: 17kB
         ->  Seq Scan on t0  (cost=3D0.00..32.60 rows=3D2260 width=3D8) (a=
ctual time=3D0.005..0.005 rows=3D0.00 loops=3D1)
 Planning:
   Buffers: shared hit=3D68 read=3D33
 Planning Time: 4.135 ms
 Execution Time: 19992.525 ms
(12 rows)

regression=3D# vacuum analyze t0,t1;
VACUUM
regression=3D# explain analyze SELECT * FROM t0 INNER JOIN t1 ON t0.c0 !=3D=
 t1.c1;
                                                QUERY PLAN                =
                                 =

--------------------------------------------------------------------------=
---------------------------------
 Nested Loop  (cost=3D0.00..2692478.72 rows=3D100000031 width=3D16) (actua=
l time=3D0.004..0.005 rows=3D0.00 loops=3D1)
   Join Filter: (t0.c0 <> t1.c1)
   ->  Seq Scan on t0  (cost=3D0.00..0.00 rows=3D1 width=3D8) (actual time=
=3D0.004..0.004 rows=3D0.00 loops=3D1)
   ->  Seq Scan on t1  (cost=3D0.00..1442478.32 rows=3D100000032 width=3D8=
) (never executed)
 Planning:
   Buffers: shared hit=3D9
 Planning Time: 0.094 ms
 Execution Time: 0.017 ms
(8 rows)

But really that's kind of cheating, because it depends critically
on t0 being completely empty.  If we add a row there so that the
join has to do some work, there is not so much value after all:

regression=3D# insert into t0 values(1);
INSERT 0 1
regression=3D# vacuum analyze t0;
VACUUM
regression=3D# explain analyze SELECT * FROM t0 INNER JOIN t1 ON t0.c0 !=3D=
 t1.c1;
                                                          QUERY PLAN      =
                                                    =

--------------------------------------------------------------------------=
----------------------------------------------------
 Nested Loop  (cost=3D0.00..2692479.73 rows=3D100000031 width=3D16) (actua=
l time=3D0.051..11894.867 rows=3D99999999.00 loops=3D1)
   Join Filter: (t0.c0 <> t1.c1)
   Rows Removed by Join Filter: 1
   Buffers: shared hit=3D15701 read=3D426778
   ->  Seq Scan on t0  (cost=3D0.00..1.01 rows=3D1 width=3D8) (actual time=
=3D0.003..0.005 rows=3D1.00 loops=3D1)
         Buffers: shared hit=3D1
   ->  Seq Scan on t1  (cost=3D0.00..1442478.32 rows=3D100000032 width=3D8=
) (actual time=3D0.044..3853.565 rows=3D100000000.00 loops=3D1)
         Buffers: shared hit=3D15700 read=3D426778
 Planning:
   Buffers: shared hit=3D6
 Planning Time: 0.068 ms
 Execution Time: 14050.387 ms
(12 rows)

We don't optimize for the case of tables being completely
empty, because that's basically a zero-probability situation
in real-world queries.  So even though this don't-scan-the-
inner-table-when-the-outer-one-is-empty short-circuit exists
in the executor, the optimizer does not plan on the assumption
of that happening.  That's not a bug, it's intentional.
We judge that a plan made on that assumption will be too
brittle if the table turns out to not be empty after all.

			regards, tom lane