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.
That pagination issue isn't one I realised. That's very valuable, thanks.
Re:Improving Postgres text search speed
by jonatron on Saturday August 13, 2022 @10:37AM
(#32450307)
I know this now, but when I didn't and when Postgres returned the same order thousands of times, then changed for no apparent reason, it made fixing flaky tests very tricky.
Re:Improving Postgres text search speed
by irjustin on Saturday August 13, 2022 @08:31AM
(#32449280)
> That's frightening they don't know that
It's a common problem when moving from Mysql. We personally ran into that shortly after switching to Postgres, but seems like something they should've discovered a lot earlier for sure.
We shared the mistake so others don't accidentally make the same assumption; I'm sure a few people either learned it for the first time or appreciated the reminder. Rest easy this never made it to prod.
That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on order unless... scratch that onto your eyeballs so you never forget it.
Also, will people please stop posting rainbow-on-black screenshots, especially with the screenshotted text size smaller than the main text.