20. Accelerating Analytical Queries using the AlloyDB Columnar Engine

Task 1. Create Baseline Dataset for Testing the Columnar Engine

 

 

export ALLOYDB=ALLOYDB_ADDRESS

echo $ALLOYDB  > alloydbip.txt

pgbench -h $ALLOYDB -U postgres -i -s 500 -F 90 -n postgres

 

 

psql -h $ALLOYDB -U postgres

select count (*) from pgbench_accounts;

 

 

Task 2. Run a Baseline Test

\timing on

SELECT aid, bid, abalance FROM pgbench_accounts WHERE bid < 189  OR  abalance > 100 LIMIT 20;

 

 

EXPLAIN (ANALYZE,COSTS,SETTINGS,BUFFERS,TIMING,SUMMARY,WAL,VERBOSE)  SELECT count(*) FROM pgbench_accounts WHERE bid < 189  OR  abalance > 100;

 

 

Task 3. Verify the Database Flag for the Columnar Engine

 

Notice that the flag named google_columnar_engine.enabled is already enabled (status of on). You will not add an additional flag as part of this lab.

 

Task 4. Set or Verify a Database Extension for the Columnar Engine

 

1.Return to the alloydb-client shell. The psql client should still be active. If not, reconnect using the instructions in Task 1.

2.Ensure that you are connected to the postgres database by running the following query.

 

\c postgres

\dx

CREATE EXTENSION IF NOT EXISTS google_columnar_engine;

\dx

 

 

Task 5. Testing the Columnar Engine

SELECT google_columnar_engine_add('pgbench_accounts');

 

 

EXPLAIN (ANALYZE,COSTS,SETTINGS,BUFFERS,TIMING,SUMMARY,WAL,VERBOSE)  SELECT count(*) FROM pgbench_accounts WHERE bid < 189  OR  abalance > 100;

 

 

참고 : https://www.cloudskillsboost.google/paths/22/course_templates/642/labs/501234

Accelerating Analytical Queries using the AlloyDB Columnar Engine | Google Cloud Skills Boost