News for hackers. Stuff that matters.

A comment on Speeding up SQL queries by orders of magnitude using UNION. Parent comment

Re:Speeding up SQL queries by orders of magnitude using UNION by dragonwriter on Saturday March 20, 2021 @07:31PM (#26526874)
> Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs.

Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.

[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by jbverschoor on Sunday March 21, 2021 @07:07AM (#26529584)
Also common for ancient RDBMSes
[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by haimez on Saturday March 20, 2021 @10:22PM (#26527650)
Really seems like you don’t know what sargable means (and didn’t bother to find out if this is your reply).
[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by fastball on Saturday March 20, 2021 @11:35PM (#26527929)
I think it might be the other way around.
[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by epr on Sunday March 21, 2021 @11:34AM (#26531187)
No, its pretty clear from reading his entire response that he understands exactly what sargable means. "just use indexes properly" is pretty ambiguous, but the rest of his comment provides context. It doesn't make much sense to me to be indexing on fn(x) for predicates unless an alternative sargable predicate is too complex or not possible, but that doesn't mean that it's never the best solution.
[ Parent | on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION by candu on Monday March 22, 2021 @09:18AM (#26540356)
> unless an alternative sargable predicate is too complex or not possible...

As you note, functional indexes can sometimes be the best solution - some common examples: when working with different geospatial projections in PostGIS; when building a full-text index using `to_tsvector`. For anyone who works with relational databases that support this, definitely a good tool to have in your toolkit!

[ Parent | on HN ]