Can adding an index make a non SARGable query SARGable?

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)

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by bfung on Sunday October 20, 2019 @05:54PM (#21307443)

If one thinks about how that index is created underneath the covers, it's basically precomputing that column and using it as an index! =)

[ Parent | on HN ]

Re:Can adding an index make a non SARGable query SARGable? by Tostino on Monday October 21, 2019 @09:48AM (#21312179)

Yes exactly. But it works very well in practice.

[ Parent | on HN ]