News for hackers. Stuff that matters.
Re:Improving Postgres text search speed by silvestrov on Saturday August 13, 2022 @07:11AM (#32448735)
Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries (or appends a "random()" clause to an existing "order by" clause).

Or even better: have it as a connection/session parameter.

Re:Improving Postgres text search speed by zasdffaa on Saturday August 13, 2022 @07:27AM (#32448846)
> Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries

interesting! I like it.

> or appends a "random()" clause to an existing "order by" clause

That one I don't understand. If the Order By is there, why randomise it? I guess

   order by p, q, r, random()
then it sort of allows you to find out if it's been ordered sufficiently for your needs - is that it?
Re:Improving Postgres text search speed by rubyist5eva on Saturday August 13, 2022 @08:37AM (#32449320)
Sufficient yes because if, for example, you’re simply sorting by a value that’s shared/duplicated like enums values, the ordering of the ordered groups is basically up to the query planner and can change (one query may use an index while another does a sequential scan, for example). The most common use case where I’ve seen this be significant is offset based pagination where the query plan actually changes depending on which page you’re on. You’ll either miss records or even get the a few rows back from the previous page because of the different ordering chosen by the planner/optimizer.
Re:Improving Postgres text search speed by zasdffaa on Sunday August 14, 2022 @02:02AM (#32456814)
That pagination issue isn't one I realised. That's very valuable, thanks.