exists? shouldn't remove the order_by clause

[DaemonSnake]

Elixir version

1.19.4

Database and Version

Postgresql 17.5

Ecto Versions

3.13.5

Database Adapter and Versions (postgrex, myxql, etc)

ecto_sql 3.13.3 (postgresql)

Current behavior

the exists? function currently makes changes to the give query.
All these changes are expected but one:

      |> Query.exclude(:order_by)

While it's logical to not care about ordering when looking if a record exists,
order_by has an impact on the query plan and the performance of the query in postgres
where limit: 1 is provided

Here is a small example:

explain
SELECT
	TRUE
FROM
	"permission_targets" AS p0
WHERE
	(p0."removed_at" IS NULL)
	AND (p0."workspace_id" = '...')
LIMIT
	1
;
-- produces the following query plan:
-- Limit  (cost=0.00..0.44 rows=1 width=1)
--  ->  Seq Scan on permission_targets p0  (cost=0.00..51834.21 rows=118618 width=1)
--"        Filter: ((removed_at IS NULL) AND (workspace_id = '...'::uuid))"

Same query with an order_by on workspace_id, id with the right index

explain
SELECT
	TRUE
FROM
	"permission_targets" AS p0
WHERE
	(p0."removed_at" IS NULL)
	AND (p0."workspace_id" = '...')
order by p0.id
LIMIT
	1
;

-- produces the following query plan:
-- Limit  (cost=0.05..0.54 rows=1 width=33)
-- "  ->  Index Only Scan using ""permission_targets_workspace_id_id"" on permission_targets p0  (cost=0.05..57654.03 rows=118618 width=33)"
-- "        Index Cond: (workspace_id = '...'::uuid)"

The above query is a simplified version of a hot-path query that can spike to 9s in avg and 2.74 Load by Waits for just 0.1 calls/sec

Temporary fix will be to use .one instead

Expected behavior

exists? shouldn't alter the order_by clause of the query as:

• it doesn't affect the exists? logic
• changes the query plan of the query
• doesn't trigger a sort because the query has a select(1) clause

[josevalim]

Please do send a PR. I guess databases would be able to optimize those cases anyway if they find the ORDER BY to be irrelevant.

[DaemonSnake]

Thanks for the quick reply, created the following PR and updated the tests
#4692

[josevalim]

Closing in favor of the PR.