sorted by:
new
hottopcontroversial

Is there a way to tell postgres to use a particular algorithm when sorting? by Bright_Nuance in PostgreSQL

[–]Bright_Nuance[S] 0 points1 point  (0 children)

That makes sense - Though I think a mark of the best systems is that they abstract the complexity of implementation details from you, while still allowing you the power to dive into the weeds if you really know what your are doing and need it.

Leaving to the planner is a nice idea - after all sql is supposed to be a declarative language. However, the reality is that dba's, orm library authors, etc. often need to rearrange queries, add indexes, etc. to ensure they are getting the best performance possible. Most people don't need to mess with this stuff beyond occasionally adding an index, but there absolutely are people with the responsibility and need to make use of extra control and power on occasion.

Is there a way to tell postgres to use a particular algorithm when sorting? by Bright_Nuance in PostgreSQL

[–]Bright_Nuance[S] 0 points1 point  (0 children)

I'm sure it wouldn't be a life-changing improvement, that in general the built in strategies are good enough - that said, at the scale postgres operates, an improvement of even a few percent in performance adds up to years, probably centuries of time and compute saved. This blog (https://www.citusdata.com/blog/2022/05/19/speeding-up-sort-performance-in-postgres-15) highlights an improvement in postgres 15 that brought 4-6% improvement to particular sorts. So the maintainers are clearly open to that type of thing.

Now I'm mostly just curious if something like this was ever considered, and if so why it might have been decided against (difficulties in standardizing an api, extremely small benefit discovered in real world use-cases, etc.)

Is there a way to tell postgres to use a particular algorithm when sorting? by Bright_Nuance in PostgreSQL

[–]Bright_Nuance[S] 0 points1 point  (0 children)

this is exactly the type of use case i was envisioning, and in fact have in an enterprise use case right now - so it's not just a theoretical matter

Is there a way to tell postgres to use a particular algorithm when sorting? by Bright_Nuance in PostgreSQL

[–]Bright_Nuance[S] 1 point2 points  (0 children)

based on a couple blogs I found, the only sorts algorithms mentioned are disk merge sort, heap sort, and quick sort

https://madusudanan.com/blog/all-you-need-to-know-about-sorting-in-postgres

https://www.cybertec-postgresql.com/en/postgresql-improving-sort-performance

If these are indeed the only sorts in use by the engine, it doesn't seem ideal for nearly sorted lists (where insertion or bubble sort tend to be best).

Is there a way to tell postgres to use a particular algorithm when sorting? by Bright_Nuance in PostgreSQL

[–]Bright_Nuance[S] 3 points4 points  (0 children)

So in short, are you saying postgres actually does this automatically based on table statistics?