Hacker News new | ask | show | jobs
by jwcacces 2428 days ago
In postgres you could just make an index on the RIGHT(SomeColumn,3) expression.

See https://www.postgresql.org/docs/current/sql-createindex.html

> An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

No need to maintain a separate view or its index
1 comments
hotsauceror 2428 days ago
That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to have computed indexes like that.
link
marzell 2428 days ago
I mean, you could add a new computed column to a table and index that... It's really effectively the same thing, just more visible. Still requires a table scan and storage/maintenance of the index/column. I guess technically it may require additional storage but it's not radically different. Either way, still requires some foresight to implement it before your queries require it so neither is a magic bullet.
link
hotsauceror 2427 days ago
Right. What you’d really need would be for the query optimizer to recognize your non-sargable expression and be able to look up a corresponding function index, and then have a mechanism for seeking on that. Indexing a computed column, like you said, wouldn’t actually solve the problem of having to evaluate each row to begin with.
link
marzell 2427 days ago
Does a function index somehow eliminate the need for RBAR evaluation? I don't see how it's functionally any different in this context from a computed column
link
hotsauceror 2426 days ago
I don’t know how postgresql does it, but I’m interpreting it as yes, their engine has a way to actually replace nonsargable expressions in the query plan itself, with some other operation that performs a seek against the index on the filter. Otherwise you’re right, you’d still need an RBAR evaluation, which is really the crux of the issue. I wish they’d reframe this discussion as RBAR instead of sargability, because it covers so many more sins...
link