Elide a provably-redundant DISTINCT on single-table queries

rubyonrails-core
1

Hi all — I’d like feedback on a small Active Record optimization before it gets too far. PR is up at Elide redundant DISTINCT when it cannot remove rows by kilaru · Pull Request #58029 · rails/rails · GitHub , but per the contributing guide I wanted to raise it here since it changes generated SQL.

The problem

When a relation is distinct, selects from a single table (no joins, eager loading, from subquery, or GROUP BY), and projects the whole row, every returned row already carries a unique primary key. The result is therefore already distinct, and DISTINCT only forces the database into an unnecessary sort/unique pass.

This shows up a lot in practice — e.g. a scope that adds .distinct defensively (safe when joins might be chained later, wasteful when they aren’t), or a plain Model.where(...).distinct.

On PostgreSQL the cost is a Unique (+ Sort) node over the full, wide result set:

EXPLAIN ANALYZE, 1,000,000-row single table, warm cache:

SELECT * FROM widgets 113 ms Seq Scan SELECT DISTINCT * FROM widgets 383 ms Unique + Incremental Sort

Both return the same 1,000,000 rows — the ~3x is pure overhead. (I first hit this on a production table where a single-table SELECT DISTINCT "t".* over millions of wide rows spent seconds in an external-merge sort that removed nothing.)

The proposal

Relation#build_arel omits DISTINCT only when it is provably a no-op:

  • single table (no joins / left_outer_joins / includes/eager load / references / from / GROUP BY / HAVING), and
  • whole-row projection (empty select → SELECT "t".*, or explicit * / "t".*), and
  • the model has a primary key.
Post.distinct.to_sql
# before => SELECT DISTINCT "posts".* FROM "posts"
# after  => SELECT "posts".* FROM "posts"

It’s deliberately conservative — a false positive would silently drop rows, so anything it can’t prove keeps DISTINCT:

  • joins / eager loading keep it (fan-out can duplicate rows);
  • a model without a primary key keeps it (a keyless table can hold fully-identical rows, so DISTINCT is meaningful);
  • explicit non-whole-row selects keep it;
  • COUNT(DISTINCT …) is untouched (it’s built via a separate calculation path that sets distinct!(false) before build_arel).

The result set is never changed — it’s purely dropping a sort that can’t remove anything.

This is complementary to #55029 / #55027 (which target unnecessary COUNT(DISTINCT) on eager-loaded counts); this is the non-count, top-level DISTINCT.

Questions for the team

  1. Always-on vs. gated? Since it’s provably a no-op (identical results), I’ve made it unconditional. Would you prefer it behind a config / framework default instead?
  2. Scope. I limited it to the whole-row projection to avoid any SQL parsing. An explicit primary-key select (select(:id).distinct) could be a follow-up, but it has one wrinkle on PG — select(:id).distinct.or der(:created_at) currently raises (“ORDER BY must appear in the select list”), and eliding DISTINCT would make it succeed. The whole-row scope doesn’t hit that. Is whole-row-only the right starting point?
  3. Any objections to Active Record second-guessing an explicit .distinct, even when it’s provably redundant?

Happy to iterate. Thanks for taking a look!