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:
Code: Select all
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;
------------------------------
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
| +---> 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')