Re: Is there any chance to get some kind of a result set sifting mechanism in Postgres? - Mailing list pgsql-hackers

From Wolfgang Wilhelm
Subject Re: Is there any chance to get some kind of a result set sifting mechanism in Postgres?
Date
Msg-id 1620765473.2321666.1715593714608@mail.yahoo.com
Whole thread Raw
In response to Is there any chance to get some kind of a result set sifting mechanism in Postgres?  (aa <ghevge@gmail.com>)
Responses Re: Is there any chance to get some kind of a result set sifting mechanism in Postgres?
List pgsql-hackers
Hi,

do I interpret your idea correctly: You want some sort of ordering without ordering?

Kind regards
WW

Am Montag, 13. Mai 2024 um 10:40:38 MESZ hat aa <ghevge@gmail.com> Folgendes geschrieben:


Hello Everyone!

Is there any chance to get some kind of a result set sifting mechanism in Postgres? 

What I am looking for is a way to get for example: "nulls last" in a result set, without having to call "order by" or having to use UNION ALL, and if possible to get this in a single result set pass.

Something on this line: SELECT a, b, c FROM my_table WHERE a nulls last OFFSET 0 LIMIT 25

I don't want to use order by or union all because these are time consuming operations, especially on  large data sets and when comparations are done on dynamic values (eg: geolocation distances in between a mobile and a static location) 

What I would expect from such a feature, will be speeds comparable with non sorted selects, while getting a very rudimentary ordering.

A use case for such a mechanism will be the implementation of QUICK relevant search results for a search engine.

I'm not familiar with how Postgres logic handles simple select queries, but the way I would envision a result set sifting logic, would be to collect the result set, in 2 separate lists, based on the sifting condition, and then concatenate these 2 lists and return the result, when the pagination requests conditions are met.

Any idea if such a functionality is feasible ?

Thank you.

  PS: if ever implemented, the  sifting mechanism could be extended to accommodate any type of thresholds, not just null values.



pgsql-hackers by date:

Previous
From: Dmitry Koval
Date:
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Next
From: Anton Voloshin
Date:
Subject: Re: 039_end_of_wal: error in "xl_tot_len zero" test