I have a table named fact_table
I created a projection named p1_fact_table
I updated the stats:
select analyze_statistics ('schema_name.fact_table') ;
I query the projections table to confirm the new projection has stats:
select * from projections where has_statistics = false and projection_schema = 'schema_nme' order by anchor_table_name ;
It shows up in the results, sot he projection does not have stats (has_statistics = false).
Any idea why? Is it because I didn't follow Vertica's projection naming standards?
https://my.vertica.com/docs/7.1.x/HTML/ ... Naming.htm
Thanks for your help!
projection stats not getting updated
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: projection stats not getting updated
I'm pretty sure you can't get stats until data has been inserted. Have you ever insert data into the table? See example below:
Code: Select all
dbadmin=> create table test (c int);
CREATE TABLE
dbadmin=> create projection test_pr as select * from test;
CREATE PROJECTION
dbadmin=> select analyze_statistics('test');
analyze_statistics
--------------------
0
(1 row)
dbadmin=> select has_statistics from projections where projection_name = 'test_pr';
has_statistics
----------------
f
(1 row)
dbadmin=> insert into test values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select analyze_statistics('test');
analyze_statistics
--------------------
0
(1 row)
dbadmin=> select has_statistics from projections where projection_name = 'test_pr';
has_statistics
----------------
t
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: projection stats not getting updated
Good point. I should have mentioned that the table has data.
Also, I ran start_refresh and then queried the projection_storage table to confirm that the projection has data.
Using Amazon AMI 7.1.2-1
Also, I ran start_refresh and then queried the projection_storage table to confirm that the projection has data.
Using Amazon AMI 7.1.2-1
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: projection stats not getting updated
Make sure the new projection isn't still refreshing when you ran the analyze_statistics() function. Check the PROJECTION_REFRESHES table to make sure it is not executing and is finished.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: projection stats not getting updated
Made sure the that refresh was finished.
Then ran analyze_statistics.
Where create_type (in the projections table) is "CREATE_PROJECTION" has_statistics = false.
Where it is "CREATE_TABLE" has_statistics is true.
K-safety = 1, all nodes for these projections are populated with data.
I am able to reproduce this scenario consistently.
Then ran analyze_statistics.
Where create_type (in the projections table) is "CREATE_PROJECTION" has_statistics = false.
Where it is "CREATE_TABLE" has_statistics is true.
K-safety = 1, all nodes for these projections are populated with data.
I am able to reproduce this scenario consistently.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: projection stats not getting updated
If you can give us something to reproduce on our end, that would be helpful.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: projection stats not getting updated
Hi Victorgm,
I've tried all kinds of ways to reproduce your scenario but cannot. Like Norbert asked, can you please post a simple step by step example of what you are doing (i.e. copy and past a vsql session).
Note, in your original post, you were using this query:
I've tried all kinds of ways to reproduce your scenario but cannot. Like Norbert asked, can you please post a simple step by step example of what you are doing (i.e. copy and past a vsql session).
Note, in your original post, you were using this query:
Make sure the projection schema name you are using is correct. Note that a projection's schema isn't always the equal to the anchor table's schema...select * from projections where has_statistics = false and projection_schema = 'schema_nme' order by anchor_table_name ;
Code: Select all
dbadmin=> create schema schema1;
CREATE SCHEMA
dbadmin=> create schema schema2;
CREATE SCHEMA
dbadmin=> create table schema1.test (c) as select 1;
CREATE TABLE
dbadmin=> create projection schema2.test_pr as select * from schema1.test;
WARNING 4468: Projection <schema2.test_pr> 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 distinct projection_schema, table_schema, anchor_table_name
dbadmin-> from projections join tables
dbadmin-> on anchor_table_id = table_id
dbadmin-> where anchor_table_name = 'test';
projection_schema | table_schema | anchor_table_name
-------------------+--------------+-------------------
schema1 | schema1 | test
schema2 | schema1 | test
(2 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.