May I specity which projection to be used in the query?

[adasuke2101]

hi
May I specity which projection to be used in the query? like oracle hint

[JimKnicely]

There is an optimizer hint called "UseOnlyProjections" that may be of value to you.

Example:

Code: Select all

dbadmin=> create table test (c1 int, c2 int) order by c1;
CREATE TABLE

dbadmin=> create projection use_this_one as select * from test order by c2;
CREATE PROJECTION

dbadmin=> explain select * from test;
                                                                                         QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain select * from test;

 Access Path:
 +-STORAGE ACCESS for test [Cost: 2, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.test_super
 |  Materialize: test.c1, test.c2

Notice that the above EXPLAIN PLAN shows that the public.test_super projection is used.

Code: Select all

dbadmin=> select set_optimizer_directives('UseOnlyProjections=use_this_one');
                               set_optimizer_directives
--------------------------------------------------------------------------------------

Optimizer Directives
----------------------
UseOnlyProjections=public.use_this_one

(1 row)

dbadmin=> explain select * from test;
                                                                                         QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 Optimizer Directives
 ----------------------
 UseOnlyProjections=public.use_this_one


 explain select * from test;

 Access Path:
 +-STORAGE ACCESS for test [Cost: 606, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.use_this_one
 |  Materialize: test.c2, test.c1

Notice that the above EXPLAIN PLAN shows that the public.use_this_one projection is being used now.

[NorbertKrupa]

You can also set an optimizer directive to ignore projections during optimization.