Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

Considering how easy the workaround for Ordering by NULLS LAST is (asking for ordered non-null values, then for null values) I wonder why postgres doesn't do that optimization internally. At first glance it seems like you could trivially rewrite any ORDER BY x NULLS LAST query that can't use an index into two queries that can use the index, and then UNION them together.

Maybe one of these things that are so easy to work around that nobody has prioritized fixing them.



I suspect the latter, but also the limited win.

In practice indexes are rarely used for sorting. It's almost always more profitable to use an index for filtering on the first table, and some mix of predicate pushdown (for hash and nested loop joins) and key lookups (for nested loop joins) for joined tables. And if a merge join is applicable, it's probably on PK/FKs and not on your presumably semantic sort order. And most of the time, the set of rows responsive to a query is small enough that not using an index for a final sort isn't a big deal.

Where an index is profitable for sorting is when you're doing pagination over a mostly unbounded set of rows, potentially with a predicate that matches on a prefix of the index you're planning to use for the sort. The plans for these queries tend to be fragile, you need to take care when adding extra joins and predicates that they don't cause the optimizer to abandon the index for the sort.


I had the same thought!

Is this almost-always faster?

(If not, would there be a workaround if a user didn't want this new behavior? Postgres core team hates new settings...)

Would it be hard it would be to add to the optimizer as a query rewrite?




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: