Refreshing Projection

deb0687

I created some projection for converting hash join to merge join between 2 tables.

 

When I ran explain then its still showing hash join only but after doing select refresh(); in explain its showing merge join.

 

Should I refresh everytime when I create a projection ?

 

Please tell me some rules and process after creating projection.

Adrian_Oprea_1

When you create a projection you must populate it first.

Here is short example:

Create a simple projection on a table ;

(dbadmin@:5433) [dbadmin] > create projection test_prj1 as select id from test;
WARNING 4468:  Projection <public.test_prj1> 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

Try to run a query pointing to that projection:

(dbadmin@:5433) [dbadmin] > select * from test_prj1;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection test_prj1 not used in the plan because the projection is not up to date.

- see that you get an error, this is becouse you have created the projection but there is nothing inside it."not up to date" or "not eligeble".

 You must refresh your anchor table in order to populate the projection that depend on it.

 

In your case you probably create the projection without refresh and run the query pointing to the master projection/anchor table like i do here :

(dbadmin@:5433) [dbadmin] *> select count(*) from test;
  count
----------
 25701000
(1 row)

-- works , but is uses the super projection as seen here bellow - thou you have created the projection before to attend your needs.

(dbadmin@:5433) [dbadmin] *> explain select count(*) from test;

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

 explain select count(*) from test;

 Access Path:
 +-GROUPBY NOTHING [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Aggregates: count(*)
 | +---> STORAGE ACCESS for test [Cost: 2, Rows: 26M (1 RLE) (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: public.test_super

To make the projection availabe for use you need to refresh it , this way it will recognized by the optimizer.

see example;

(dbadmin@:5433) [dbadmin] *> select refresh('test');
                                                                                                                                           refresh                                                                                                                                    
-----------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."test_prj1": [test] [refreshed] [scratch] [0] [5]

(1 row)

(dbadmin@:5433) [dbadmin] > explain select count(*) from test;

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

 explain select count(*) from test;

 Access Path:
 +-GROUPBY NOTHING [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Aggregates: count(*)
 | +---> STORAGE ACCESS for test [Cost: 2, Rows: 26M (1 RLE) (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: public.test_prj1

You can see now that the optimizer is chosing the new created projection instead of the super projection. 

 

So the conclusion is that you need to refresh it after you create it in order for it to be used. Also there are other things to be done as well like statistics and histograms.