3 ms·

The function `Right(...)` is not indexable because... it's a function - we could apply infinite many types of function on column `SomeColumn` and it'd be unreas

by bfung 7y ago

The function `Right(...)` is not indexable because... it's a function - we could apply infinite many types of function on column `SomeColumn` and it'd be unreasonable to create index for them all, with different arguments. It depends heavily how smart the optimizer of the underlying database is in order to pick up on the fact that there's another column or index made that's precomputed for `Right(...)` with the correct arguments applied to it.

andrewf 7y ago
Postgres can do indexes on expressions. (A bitmap scan over 2 rows may not be the most impressive example!) https://www.postgresql.org/docs/12/indexes-expressional.html https://www.postgresql.org/docs/12/indexes-expressional.html foo=# create table foo(x TEXT); foo=# CREATE INDEX foo_r3 on foo (right(x,3)); foo=# INSERT INTO foo VALUES ('aaaaaaaaaaaaazzz'); foo=# INSERT INTO foo VALUES ('xxxxxxxaaa'); foo=# SELECT * FROM foo WHERE right(x,3) > 'fff'; x ------------------ aaaaaaaaaaaaazzz (1 row) foo=# EXPLAIN ANALYZE SELECT * FROM foo WHERE right(x,3) > 'fff'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on foo (cost=7.66..24.46 rows=453 width=32) (actual time=0.018..0.018 rows=1 loops=1) Recheck Cond: ("right"(x, 3) > 'fff'::text) Heap Blocks: exact=1 -> Bitmap Index Scan on foo_r3 (cost=0.00..7.55 rows=453 width=0) (actual time=0.010..0.010 rows=1 loops=1) Index Cond: ("right"(x, 3) > 'fff'::text) Planning Time: 0.072 ms Execution Time: 0.063 ms (7 rows)
- bfung 7y ago
  If one thinks about how that index is created underneath the covers, it's basically precomputing that column and using it as an index! =)
  - Tostino 7y ago
    Yes exactly. But it works very well in practice.

by bfung 7y ago

andrewf 7y ago

Postgres can do indexes on expressions. (A bitmap scan over 2 rows may not be the most impressive example!) https://www.postgresql.org/docs/12/indexes-expressional.html https://www.postgresql.org/docs/12/indexes-expressional.html foo=# create table foo(x TEXT); foo=# CREATE INDEX foo_r3 on foo (right(x,3)); foo=# INSERT INTO foo VALUES ('aaaaaaaaaaaaazzz'); foo=# INSERT INTO foo VALUES ('xxxxxxxaaa'); foo=# SELECT * FROM foo WHERE right(x,3) > 'fff'; x ------------------ aaaaaaaaaaaaazzz (1 row) foo=# EXPLAIN ANALYZE SELECT * FROM foo WHERE right(x,3) > 'fff'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on foo (cost=7.66..24.46 rows=453 width=32) (actual time=0.018..0.018 rows=1 loops=1) Recheck Cond: ("right"(x, 3) > 'fff'::text) Heap Blocks: exact=1 -> Bitmap Index Scan on foo_r3 (cost=0.00..7.55 rows=453 width=0) (actual time=0.010..0.010 rows=1 loops=1) Index Cond: ("right"(x, 3) > 'fff'::text) Planning Time: 0.072 ms Execution Time: 0.063 ms (7 rows)

bfung 7y ago
If one thinks about how that index is created underneath the covers, it's basically precomputing that column and using it as an index! =)
- Tostino 7y ago
  Yes exactly. But it works very well in practice.