How to create projection for proper using of UPPER function? we've faced the situation when the operation WHERE UPPER(columnName) IN ('PRED1', 'PRED2', 'PRED3') works very bad. If I remove UPPER then it performs good. I've tried to play with projections and could not get it working properly. How can I solve that issue (maybe the problem is not in projection...)
I've tried following code:

CREATE TABLE public.ProjectionsTest (
id int primary key,
a varchar(2000)
);


INSERT INTO public.ProjectionsTest values (1,'a');
INSERT INTO public.ProjectionsTest values (2,'b');
INSERT INTO public.ProjectionsTest values (3,'c');
INSERT INTO public.ProjectionsTest values (4,'d');
INSERT INTO public.ProjectionsTest values (5,'e');


SELECT ANALYZE_STATISTICS('public.ProjectionsTest');
SELECT REFRESH('public.ProjectionsTest');


CREATE PROJECTION public.ProjectionsTest_lower_ob
(
 id,
 a
)
AS
 SELECT id,
        lower(a) a
 FROM public.ProjectionsTest
 ORDER BY lower(a), id
SEGMENTED BY hash(id) ALL NODES ;


explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id;

-- ROLLBACK
DROP PROJECTION public.ProjectionsTest_lower_ob;
DROP TABLE  public.ProjectionsTest;


And I am getting following result (the projection "public.ProjectionsTest_lower_ob" is not used):

------------------------------ 
QUERY PLAN DESCRIPTION: 
------------------------------

explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id

Access Path:
+-SORT [Cost: 36, Rows: 4] (PATH ID: 1)
| Order: ProjectionsTest.a ASC, ProjectionsTest.id ASC
| +---&gt; STORAGE ACCESS for ProjectionsTest [Cost: 35, Rows: 4] (PATH ID: 2)
| | Projection: public.ProjectionsTest_super
| | Materialize: ProjectionsTest.a, ProjectionsTest.id
| | Filter: (lower(ProjectionsTest.a) = 'a')
Here is small example of how you can improve the query speed.

One way i do it is by using bucket columns based on default value at load time.(this can make your load slower thou).

see sample code

drop table test_tbl;
CREATE TABLE
    test_tbl
    (
        id INT,
        name VARCHAR(20),
        bkt_col int default(
        CASE
            WHEN name=upper(name)
            THEN 1
            WHEN name!=upper(name)
            THEN 0
        END)
    );


Create the projection to attend you predicate, segmented on replicated is up to you table type/size

CREATE PROJECTION public.test_tbl 
(
 id,
 name,
 bkt_col
)
AS
 SELECT test_tbl.id,
        test_tbl.name,
        test_tbl.bkt_col
 FROM public.test_tbl
 ORDER BY test_tbl.bkt_col,
          test_tbl.id,
          test_tbl.name
SEGMENTED BY hash(test_tbl.bkt_col, test_tbl.id, test_tbl.name) ALL NODES KSAFE 1;


Insert some data

insert into  test_tbl(id,name)  values (1,'BOB') ;
insert into  test_tbl(id,name)  values (1,'bob') ;
id  name  bkt_col  
--  ----  -------  
1   bob   0        
1   BOB   1  


To query it use this sql, this way you avoid upper at run time and your stats will be well used.

SELECT
    *
FROM
    test_tbl where bkt_col = 1 and name IN ('BOB')