Wrong Index Due To ORDER BY

Query Advisor detects when Postgres incorrectly chooses to sort first using an Index and then filter all results, often scanning millions of rows unnecessarily to return a small result set.

The problem

When queries combine WHERE conditions with ORDER BY and LIMIT, Postgres sometimes optimizes for quick sorting instead of efficient filtering. This can result in scanning massive amounts of data when only a few rows are needed.

Example scenario

SELECT * FROM large_table
WHERE database_id = 123
ORDER BY created_at DESC
LIMIT 50;

If Postgres chooses an index on created_at for its sort order, it may scan millions of rows to find 50 that match database_id = 123, instead of efficiently filtering first with an index on database_id.

How Query Advisor helps

When Query Advisor detects this pattern, it provides query rewrites that force Postgres to filter efficiently before sorting.

Typical optimization approach

Query Advisor often suggests a simple modification to prevent inefficient sorting:

SELECT * FROM large_table
WHERE database_id = 123
ORDER BY created_at + 0 DESC  -- The "+0" forces not using an index on created_at
LIMIT 50;

This small change forces Postgres to use the database_id index for filtering first, then sort only the matching rows. The result is identical, but performance often improves by orders of magnitude.

Next steps

• See complete example - Full walkthrough of Query Advisor optimization workflow
• Configure Workbooks - Set up testing environment for validating optimizations
• Set up alerts - Get notified when new optimization opportunities are detected