Re: Should we optimize the `ORDER BY random() LIMIT x` case?

On 15/5/2025 01:41, Aleksander Alekseev wrote:
> One can do `SELECT (the query above) ORDER BY random() LIMIT x` but
> this produces an inefficient plan. Alternatively one could create
> temporary tables using `CREATE TEMP TABLE ... AS SELECT * FROM tbl
> TABLESAMPLE BERNOULLI(20)` but this is inconvenient and would be
> suboptimal even if we supported global temporary tables.
> 
> 1. Do you think there might be value in addressing this issue?
> 2. If yes, how would you suggest addressing it from the UI point of
> view - by adding a special syntax, some sort of aggregate function, or
> ...?
I think I got your point, but just to be sure:
Do you want to have some random sampling from an arbitrary subquery with 
the guarantee that N distinct (by tid) tuples will be produced or all 
the tuples if the underlying subquery produces less than N?

What kind of optimisation trick may the optimiser use here to provide an 
optimal plan? As I see it, it will need to think that all the tuples 
should be returned from the subquery. The only profit is to skip sorting 
the massive sample.

As a palliative, you may laterally join your subquery with a stored 
procedure, which will process the incoming tuple and implement the logic 
of random sampling.

Implementation of that in the core will need a new "skip result" node 
and new syntax, which may be too much if a workaround is found.

-- 
regards, Andrei Lepikhov