Learn How the Column-Store Meta Scan Accelerates Query Performance in AnalyticDB for PostgreSQL

Implementation of the Meta Scan Function

  • Row group metadata: The metadata of each table consists of multiple row group metadata records. The maximum and minimum values of each row group metadata record reflect the metadata of 10,000 consecutive rows. Each row group metadata record contains 10 row batch metadata records. As shown in the figure above, row group metadata uses this formula: (min, max) = (1, 4000).
  • Row batch metadata: The max and min values of each row batch metadata record reflect the metadata of 1,000 consecutive rows. Unlike row group metadata, row batch metadata also records the offset of the block where the first row to be overwritten resides.
  • Supported data types: int2, int4, and int8; float4 and float8; time, timetz, timestamp, and timestamptz; varchar, text, and bpchar; and cash.
  • Supported operators: =, <, <=, >, and >=.

TPC-H Test

Upgrade Your Existing Instances with the Meta Scan Function

CREATE OR REPLACE FUNCTION UPGRADE_AOCS_TABLE_META(tname TEXT) RETURNS BOOL AS $$
DECLARE
tcount INT := 0;
BEGIN
-- CHECK TABLE NAME
EXECUTE 'SELECT COUNT(1) FROM PG_AOCSMETA WHERE RELID = ''' || tname || '''::REGCLASS' INTO tcount;
IF tcount IS NOT NULL THEN
IF tcount > 1 THEN
RAISE EXCEPTION 'found more than one table of name %', tname;
ELSEIF tcount = 0 THEN
RAISE EXCEPTION 'not found target table in pg_aocsmeta, table name:%', tname;
END IF;
END IF;
EXECUTE 'ALTER TABLE ' || tname || ' SET WITH(REORGANIZE=TRUE)';
RETURN TRUE;
END;
$$ LANGUAGE PLPGSQL;select UPGRADE_AOCS_TABLE_META(tname);
-- disable metascan
set rds_enable_column_meta_scan = off;
-- enable metascan
set rds_enable_column_meta_scan = on;
-- show metascan is enable?
show rds_enable_column_meta_scan;

Conclusion

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com