projection stats not getting updated

Moderator: NorbertKrupa

Victorgm
Beginner
Beginner
Posts: 25
Joined: Fri Jul 17, 2015 2:22 pm

projection stats not getting updated

Post by Victorgm » Fri Aug 21, 2015 7:16 pm

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!

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

Re: projection stats not getting updated

Post by JimKnicely » Fri Aug 21, 2015 7:24 pm

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

Image

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

Victorgm
Beginner
Beginner
Posts: 25
Joined: Fri Jul 17, 2015 2:22 pm

Re: projection stats not getting updated

Post by Victorgm » Fri Aug 21, 2015 7:38 pm

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

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

Re: projection stats not getting updated

Post by JimKnicely » Fri Aug 21, 2015 7:42 pm

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

Image

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

Victorgm
Beginner
Beginner
Posts: 25
Joined: Fri Jul 17, 2015 2:22 pm

Re: projection stats not getting updated

Post by Victorgm » Fri Aug 21, 2015 9:21 pm

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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: projection stats not getting updated

Post by NorbertKrupa » Fri Aug 21, 2015 9:32 pm

If you can give us something to reproduce on our end, that would be helpful.
Checkout vertica.tips for more Vertica resources.

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

Re: projection stats not getting updated

Post by JimKnicely » Sun Aug 23, 2015 4:13 pm

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:
select * from projections where has_statistics = false and projection_schema = 'schema_nme' order by anchor_table_name ;
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...

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

Image

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

Post Reply

Return to “Vertica Performance Tuning”