BUG #18973: The default enable_material=ON affects the cost estimation of optimizer, resulting in 10968x slow

The following bug has been logged on the website:

Bug reference: 18973
Logged by: Jinhui
Email address: jinhui-lai(at)foxmail(dot)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