0

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
| +---> 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')
  • No experience on columnstore or vertica. But basic rule in databases is that you should not use function(column) in where clause. Instead use WHERE column = uppper('input'), if possible: this means that your data in should be all upper or all lower in database and then use upper/lower to search value – simplexio Oct 6 '16 at 13:07
  • @simplexio thanks for your comment. You are right, however in that case you are not able to return the data in the same case as it was originally inserted. I know that in row stored dbs you can use index by that function but Vertica does not have indexes – Dmitrij Kultasev Oct 6 '16 at 14:20
  • I don't think Vertica can do a query rewrite in such case; you'll need to query the new projection instead of the anchor table to take advantage of the calculated column. – mustaccio Oct 6 '16 at 14:51
1

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') 
|improve this answer|||||
  • I am not sure that it is what it was asked. Let's say that I have record 'New York'. If a user asks for 'new york' then he is supposed to get this record. For that purpose the filter is supposed WHERE UPPER(col1) = 'NEW YORK'. How can I solve that with your solution? Using DEFAULT UPPER(col1) can solve it partially if the column is not updating. – Dmitrij Kultasev Nov 5 '16 at 6:41
  • - just out of curiosity did you actually create the projection with the function on the column ? because Vertica trows an error [Vertica][VJDBC](6333) ROLLBACK: Meta-functions or non-deterministic functions cannot be used in projection column expressions – Up_One Nov 6 '16 at 23:05
  • Ok , so you want him to treat it like case insensitive ! ? – Up_One Nov 6 '16 at 23:09
  • no matter what predicate value you have ! you want it to be treated like case insensitive thru projection definition ? – Up_One Nov 6 '16 at 23:21
  • The projection seemed to be the right way to solve my problem. The problem is that UPPER(col1) = UPPER('A') works much worse than col1 = 'A'. User can enter any 'a' or 'A'. To compare that with the best performance in the application layer we do the UPPERCASE for the string as 'A' and then I need to compare it in db. – Dmitrij Kultasev Nov 7 '16 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.