A specific case of #54588 which is particularly impactful is when unnecessary index joins occur for the partial index validation query performed when adding a new partial index. This type of query results in an index join operating on every row in the partial index.

For example:

defaultdb> CREATE TABLE t (a INT, b INT, INDEX a_idx (a) WHERE b > 0);
CREATE TABLE

defaultdb> SELECT count(1) FROM t@a_idx WHERE b > 0;
  count
---------
      0
(1 row)

defaultdb> EXPLAIN SELECT count(1) FROM t@a_idx WHERE b > 0;
                    info
--------------------------------------------
  distribution: full
  vectorized: true

  • group (scalar)
  │
  └── • index join
      │ table: t@primary
      │
      └── • scan
            missing stats
            table: t@a_idx (partial index)
            spans: FULL SCAN
(12 rows)

We should add an exploration rule to eliminate these type of index joins so that adding a partial index to a large table is not significantly more expensive than adding a non-partial index.

The workaround for now is to create a partial index that stores all the columns that are referenced in its predicate.