Re:Can adding an index make a non SARGable query SARGable?
by andrewf on Sunday October 20, 2019 @05:23AM
(#21303074)
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
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)
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.