From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jinhui-lai@foxmail.com
Subject: BUG #18973: The default enable_material=ON affects the cost estimation of optimizer, resulting in 10968x slow
Date: Mon, 30 Jun 2025 13:44:19 +0000
Message-ID: <18973-3bc2a3f826a72cce@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18973
Logged by: Jinhui
Email address: jinhui-lai@foxmail.com
PostgreSQL version: 17.5
Operating system: ubuntu 22.04
Description:
Dear PG developers,
Thank you for taking the time to read my report.
I may have found a performance issue. The parameter enable_material is set
to ON by default, and it affects the cost estimation of optimizer, resulting
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 != t1.c1;
c0 | c1
----+----
(0 rows)
Time: 9794.016 ms (00:09.794)
SET enable_material = off;
SELECT * FROM t0 INNER JOIN t1 ON t0.c0 != t1.c1;
c0 | c1
----+----
(0 rows)
Time: 0.893 ms
The enable_material=ON also affects CROSS/NATURAL JOIN, but not affects
LEFT JOIN:
SELECT * FROM t0 NATURAL JOIN t1;
c0 | c1
----+----
(0 rows)
Time: 7350.216 ms (00:07.350)
SELECT * FROM t0 CROSS JOIN t1;
c0 | c1
----+----
(0 rows)
Time: 6823.532 ms (00:06.824)
SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 != t1.c1;
c0 | c1
----+----
(0 rows)
Time: 0.798 ms
Adding the following code in
postgres/blob/master/src/backend/optimizer/util/plancat.c may works
#include "catalog/pg_statistic_history.h"
...
bool is_table_vacuumed_or_analyzed(Oid relid)
{
Relation pgstahis = NULL;
SysScanDesc scan = NULL;
ScanKeyData key[1];
HeapTuple tuple = NULL;
bool found = false;
ScanKeyInit(&key[0], Anum_pg_statistic_history_starelid,
BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relid));
pgstahis = relation_open(StatisticHistoryRelationId, AccessShareLock);
scan = systable_beginscan(pgstahis, StatisticHistoryTabTypAttnumIndexId,
true, NULL, 1, key);
if (HeapTupleIsValid(tuple = systable_getnext(scan))) {
found = true;
}
systable_endscan(scan);
relation_close(pgstahis, AccessShareLock);
return found;
}
Best regard,
Jinhui
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: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jinhui-lai@foxmail.com
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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox