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
