How to create projection for proper using of UPPER function?

Moderator: NorbertKrupa

Post Reply
dkultasev
Newbie
Newbie
Posts: 2
Joined: Fri Sep 23, 2016 10:07 am

How to create projection for proper using of UPPER function?

Post by dkultasev » Thu Oct 06, 2016 12:29 pm

Hi,
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;
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')

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to create projection for proper using of UPPER function?

Post by JimKnicely » Thu Oct 06, 2016 7:53 pm

Hi,

To access expression columns in "projections with expressions", you have to query the projection directly:

Code: Select all

dbadmin=> explain SELECT id, a FROM public.ProjectionsTest_lower_ob WHERE a = 'a' ORDER BY a, id;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain SELECT id, a FROM public.ProjectionsTest_lower_ob WHERE a = 'a' ORDER BY a, id;

 Access Path:
 +-STORAGE ACCESS for ProjectionsTest_lower_ob [Cost: 11, Rows: 9 (NO STATISTICS)] (PATH ID: 2)
 |  Projection: public.ProjectionsTest_lower_ob
 |  Materialize: ProjectionsTest_lower_ob.a, ProjectionsTest_lower_ob.id
 |  Filter: (ProjectionsTest_lower_ob.a = 'a')
Perhaps a better solution would be to store the data how you want to query it...

Example:

Table:

Code: Select all

dbadmin=> CREATE TABLE public.ProjectionsTest (
dbadmin(>  id int primary key,
dbadmin(>  a varchar(2000),
dbadmin(>  a_upper varchar(2000) default upper(a),
dbadmin(>  a_lower varchar(2000) default lower(a)
dbadmin(> );
CREATE TABLE

dbadmin=> INSERT INTO public.ProjectionsTest values (1,'a');
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO public.ProjectionsTest values (2,'b');
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO public.ProjectionsTest values (3,'c');
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO public.ProjectionsTest values (4,'d');
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO public.ProjectionsTest values (5,'e');
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
Query Specific Projections:

Code: Select all

dbadmin=> CREATE PROJECTION public.ProjectionsTest_lower_ob
dbadmin-> (
dbadmin(>  id,
dbadmin(>  a,
dbadmin(>  a_lower
dbadmin(> )
dbadmin-> AS
dbadmin->  SELECT id,
dbadmin->         a,
dbadmin->         a_lower
dbadmin->  FROM public.ProjectionsTest
dbadmin->  ORDER BY a_lower, id
dbadmin-> SEGMENTED BY hash(id) ALL NODES ;
WARNING 4468:  Projection <public.ProjectionsTest_lower_ob> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> CREATE PROJECTION public.ProjectionsTest_upper_ob
dbadmin-> (
dbadmin(>  id,
dbadmin(>  a,
dbadmin(>  a_upper
dbadmin(> )
dbadmin->  AS
dbadmin->  SELECT id,
dbadmin->         a,
dbadmin->         a_upper
dbadmin->  FROM public.ProjectionsTest
dbadmin->  ORDER BY a_upper, id
dbadmin-> SEGMENTED BY hash(id) ALL NODES ;
WARNING 4468:  Projection <public.ProjectionsTest_upper_ob> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

dbadmin=> SELECT ANALYZE_STATISTICS('public.ProjectionsTest');
 ANALYZE_STATISTICS
--------------------
                  0
(1 row)
Query Explain Plans:

Code: Select all

dbadmin=> explain SELECT id, a_lower a FROM public.ProjectionsTest WHERE a_lower = 'a' ORDER BY a_lower, id;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain SELECT id, a_lower a FROM public.ProjectionsTest WHERE a_lower = 'a' ORDER BY a_lower, id;

 Access Path:
 +-STORAGE ACCESS for ProjectionsTest [Cost: 10, Rows: 2] (PATH ID: 2)
 |  Projection: public.ProjectionsTest_lower_ob
 |  Materialize: ProjectionsTest.a_lower, ProjectionsTest.id
 |  Filter: (ProjectionsTest.a_lower = 'a')
 ------------------------------

dbadmin=> explain SELECT id, a_upper a FROM public.ProjectionsTest WHERE a_upper = 'A' ORDER BY a_upper, id;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain SELECT id, a_upper a FROM public.ProjectionsTest WHERE a_upper = 'A' ORDER BY a_upper, id;

 Access Path:
 +-STORAGE ACCESS for ProjectionsTest [Cost: 10, Rows: 2] (PATH ID: 2)
 |  Projection: public.ProjectionsTest_upper_ob
 |  Materialize: ProjectionsTest.a_upper, ProjectionsTest.id
 |  Filter: (ProjectionsTest.a_upper = 'A')
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “General”