Thanks very much for your help, Jim & Norbert.
Here is the setup, step-by-step.
At the bottom of the post, I included what I thought are relevant columns from the projections table - let me know if you need anything else.
One thing I noticed - table (superprojection) segmentation and custom projection segmentation do not match. Is this an issue?
-- set up the test scenario ----
create schema test ;
create table test.test_stats as select * from <source_schema>.<table> limit 1000000 ;
-- confirmed 1 M rows in anchor table:
select count(*) from test.test_stats ;
count
----------
1000000
--I queried projection_storage to confirm data is populated across all nodes
-- in the superprojection.
-- Create a custom projection:
CREATE PROJECTION test.test_stats_p1
(
col1 encoding rle,
col2 enchoding rle,
col3,
col4,
etc...
)
AS SELECT test_stats.col1,
test_stats.col2,
etc...
FROM test.test_stats
ORDER BY test_stats.col1
SEGMENTED BY hash(col1)
ALL NODES KSAFE 1;
-- I queried projection_storage to confirm creation of new empty projections on all 3 nodes.
-- populate the new query
select start_refresh() ;
-- I queried projection_storage to confirm new projections are populated across all 3 nodes.
-- So here it is - I analyze stats and but afterward the projections table
-- shows the new projection has_statistics = "false"
select analyze_statistics('test.test_stats') ;
projection_schema projection_name projection_basename anchor_table_name create_type is_up_to_date has_statistics is_segmented segment_expression
test test_stats_b0 test_stats test_stats CREATE TABLE true true true hash(col1, col2, col3, col4...)
test test_stats_b1 test_stats test_stats CREATE TABLE true true true hash(col1, col2, col3, col4...)
test test_stats_p1_b0 test_stats_p1 test_stats CREATE PROJECTION true false true hash(col1)
test test_stats_p1_b1 test_stats_p1 test_stats CREATE PROJECTION true false true hash(col1)
Thanks again!
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
Hi,
I tried to mimic your scenario (see below). I created a new DB and didn't have a table with 1,000,000 records so I created one from a file that had 1,000,000 random integers.
I created the data file using this command in the Linux prompt:
Here is my test of your scenario:
My "test_stats_p1" projection has stats...
So, what version of Vertica are you using? I have 7.1.1-12 installed.
Can you do what I did in your system to see if you get the same results?
I tried to mimic your scenario (see below). I created a new DB and didn't have a table with 1,000,000 records so I created one from a file that had 1,000,000 random integers.
I created the data file using this command in the Linux prompt:
Code: Select all
for i in {1..1000000}; do echo $[($RANDOM % 1000)]; done > test.in
Code: Select all
dbadmin=> create schema test ;
CREATE SCHEMA
dbadmin=> create table test.test_stats (col1 int) order by col1 segmented by hash(col1) all nodes ksafe 1;
CREATE TABLE
dbadmin=> copy test.test_stats from '/home/dbadmin/test.in' direct;
Rows Loaded
-------------
1000000
(1 row)
dbadmin=> create projection test.test_stats_p1 (col1 encoding rle) as select col1 from test.test_stats order by col1 segmented by hash(col1) all nodes ksafe 1;
WARNING 4468: Projection <test.test_stats_p1_b0> 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
WARNING 4468: Projection <test.test_stats_p1_b1> 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 count(*) from projection_refreshes where is_executing;
count
-------
0
(1 row)
dbadmin=> select projection_schema, projection_name, projection_basename, create_type, is_up_to_date,has_statistics,is_segmented, segment_expression from projections where anchor_table_name = 'test_stats';
projection_schema | projection_name | projection_basename | create_type | is_up_to_date | has_statistics | is_segmented | segment_expression
-------------------+------------------+---------------------+-------------------------------+---------------+----------------+--------------+-----------------------
test | test_stats_b0 | test_stats | CREATE TABLE WITH PROJ CLAUSE | t | f | t | hash(test_stats.col1)
test | test_stats_b1 | test_stats | CREATE TABLE WITH PROJ CLAUSE | t | f | t | hash(test_stats.col1)
test | test_stats_p1_b0 | test_stats_p1 | CREATE PROJECTION | t | f | t | hash(test_stats.col1)
test | test_stats_p1_b1 | test_stats_p1 | CREATE PROJECTION | t | f | t | hash(test_stats.col1)
(4 rows)
dbadmin=> select analyze_statistics('test.test_stats');
analyze_statistics
--------------------
0
(1 row)
dbadmin=> select projection_schema, projection_name, projection_basename, create_type, is_up_to_date,has_statistics,is_segmented, segment_expression from projections where anchor_table_name = 'test_stats';
projection_schema | projection_name | projection_basename | create_type | is_up_to_date | has_statistics | is_segmented | segment_expression
-------------------+------------------+---------------------+-------------------------------+---------------+----------------+--------------+-----------------------
test | test_stats_b0 | test_stats | CREATE TABLE WITH PROJ CLAUSE | t | t | t | hash(test_stats.col1)
test | test_stats_b1 | test_stats | CREATE TABLE WITH PROJ CLAUSE | t | t | t | hash(test_stats.col1)
test | test_stats_p1_b0 | test_stats_p1 | CREATE PROJECTION | t | t | t | hash(test_stats.col1)
test | test_stats_p1_b1 | test_stats_p1 | CREATE PROJECTION | t | t | t | hash(test_stats.col1)
(4 rows)
So, what version of Vertica are you using? I have 7.1.1-12 installed.
Code: Select all
dbadmin=> select version();
version
-------------------------------------
Vertica Analytic Database v7.1.1-12
(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
select version();
Vertica Analytic Database v7.1.2-1
I repeated your test and got the same results you did.
I am looking into this further and will let you know the results.
Vertica Analytic Database v7.1.2-1
I repeated your test and got the same results you did.
I am looking into this further and will let you know the results.