Re: projection stats not getting updated
Posted: Mon Aug 24, 2015 3:41 pm
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!
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!