GSP1087.md

GSP1087 - Accelerating Analytical Queries using the AlloyDB Columnar Engine

1. Navigation menu > Databases > AlloyDB for PostgreSQL > Clusters
2. Copy ip address of the cluster 10.25.0.2
3. Navigation menu > Compute Engine > VM Instances
4. Connect SSH to the instance
5. Set environment variables

export ALLOYDB=ALLOYDB_ADDRESS

6. Simpan ke AlloyDB

echo $ALLOYDB  > alloydbip.txt 

7. pgbench

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

8. Connect to AlloyDB

psql -h $ALLOYDB -U postgres

9. View table

select count (*) from pgbench_accounts;

Task 2. Run a Baseline Test

1. Hidupkan timer

\timing on

2. Run query

 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

1. Navigation menu > Databases > AlloyDB for PostgreSQL > Clusters > lab-instance > Edit Primary > Add Flag

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

1. Cek koneksi

\c postgres

2. Cek extension

\dx

Task 5. Testing the Columnar Engine

1. Add pgbench_accounts table to the columnar engine

SELECT google_columnar_engine_add('pgbench_accounts');