3 ms·
If you use a function like this, it will need to be run for every entry; doesn't that invalidate the whole point of having indexes ? What you can do is storing
by rakoo 7y ago
If you use a function like this, it will need to be run for every entry; doesn't that invalidate the whole point of having indexes ?
What you can do is storing the unaccented, simplified version of a word. Which I'm sure pg already does in a way.
- cies 7y ago> If you use a function like this, [each query] it will need to be run for every entry
- rzwitserloot 7y agoNo; if you create an index for the item: `insensitiveQuery(some_table.name)` and you then attempt to run: `SELECT * FROM some_table WHERE insensitiveQuery(name) LIKE insensitiveQuery('Étranger');`, the engine would run the `insensitiveQuery` function on the string `'Étranger'`, and then look up that result in the index directly, which is O(nlogn) or faster. If you have a million tables, that'd run the function only once (on `Étranger`) and do about 20 lookups in the index before finding you the qualifying row(s).
- cies 7y agoCheer. I did not get the index-on-a-derived-value bit.
- Mister_Snuggles 7y agoYou can also create an index on the result of the function[0]. [0] https://www.postgresql.org/docs/12/indexes-expressional.html https://www.postgresql.org/docs/12/indexes-expressional.html
- irrational 7y agoI forgot to include the index and it is too late to edit my comment. CREATE INDEX index_name ON some_table (public.insensitive_query(NAME));