Trick-or-Metric | GSP1087

Solution for Trick-or-Metric | GSP1087. 1 lab: GSP1087. Fast copy-paste commands for Google Cloud.

GSP1087 — Accelerating Analytical Queries using the AlloyDB Columnar Engine

Estimated time: 25 minutes

# 🚀 Accelerating Analytical Queries using the AlloyDB Columnar Engine | GSP1087 > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created for educational purposes, hands-on practice, and Google Cloud certification preparation. It focuses on improving analytical query performance using the AlloyDB Columnar Engine. Always attempt the lab yourself first and follow Google Cloud Skills Boost / Qwiklabs Terms of Service. This walkthrough is not affiliated with or endorsed by Go

clear
RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; BLUE=

\033[1;34m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'; WHITE=

\033[1;37m'; ORANGE=

\033[38;5;208m'
set +H

echo "${CYAN}${BOLD}"
echo "   ____       _     _ _            __   ___             "
echo "  / __ \     | |   (_) |          / _| / _ \            "
echo " | |  | |_ __| |__  _| |_   ___  | |_ | | | |_ __  ___  "
echo " | |  | | '__| '_ \| | __| / _ \ |  _|| | | | '_ \/ __| "
echo " | |__| | |  | |_) | | |_ | (_) || |  | |_| | |_) \__ \ "
echo "  \____/|_|  |_.__/|_|\__| \___/ |_|   \___/| .__/|___/ "
echo "                                            | |         "
echo "                                            |_|         "
echo "${RESET}"

BAR=$(printf '═%.0s' {1..60})
echo "${MAGENTA}${BOLD}╔${BAR}╗${RESET}"
printf "${MAGENTA}${BOLD}║${RESET}${ORANGE}${BOLD} %-58s ${RESET}${MAGENTA}${BOLD}║${RESET}\n" "BROUGHT TO YOU BY ORBIT OF OPS"
printf "${MAGENTA}${BOLD}║${RESET}${GREEN}${BOLD} %-58s ${RESET}${MAGENTA}${BOLD}║${RESET}\n" "STAGE 1 OF 3 : BASELINE DATASET (TASK 1)"
printf "${MAGENTA}${BOLD}║${RESET}${CYAN}${BOLD} %-58s ${RESET}${MAGENTA}${BOLD}║${RESET}\n" "GSP1087 | AlloyDB Columnar Engine"
echo "${MAGENTA}${BOLD}╚${BAR}╝${RESET}"

o_step() { echo; echo "${CYAN}${BOLD}▶ $*${RESET}"; }
o_ok()   { echo "  ${GREEN}${BOLD}[ OK ]${RESET} ${GREEN}$*${RESET}"; }
o_info() { echo "  ${BLUE}${BOLD}[INFO]${RESET} ${WHITE}$*${RESET}"; }
o_warn() { echo "  ${YELLOW}${BOLD}[WARN]${RESET} ${YELLOW}$*${RESET}"; }
o_bad()  { echo "  ${RED}${BOLD}[FAIL]${RESET} ${RED}$*${RESET}"; }

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  REGION=$(gcloud alloydb clusters list --project=$PROJECT_ID --filter="name~lab-cluster" --format="value(name)" 2>/dev/null | sed -n 's#.*/locations/\([^/]*\)/clusters/.*#\1#p' | head -n 1)
  [ -z "$REGION" ] && REGION=${ZONE%-*}
  [ -n "$PROJECT_ID" ] && [ -n "$ZONE" ] && [ -n "$REGION" ]
}

orbit_wait_ready() {
  local i S
  for i in $(seq 1 40); do
    S=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(state)" 2>/dev/null)
    [ "$S" = "READY" ] && return 0
    o_info "lab-instance state: ${S:-unknown} (check $i of 40)"; sleep 15
  done
  return 1
}

orbit_remote() {
  local MODE=$1 ARG=$2 i X=""
  for i in 1 2 3; do
    [ $i -eq 3 ] && X="--tunnel-through-iap"
    if [ "$MODE" = "scp" ]; then
      timeout 300 gcloud compute scp $ARG alloydb-client:~ --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X >/tmp/orbit_ssh.log 2>&1 && return 0
    else
      timeout 1500 gcloud compute ssh alloydb-client --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X --command="$ARG" >/tmp/orbit_ssh.log 2>&1 && return 0
    fi
    o_warn "SSH/SCP attempt $i of 3 failed"; tail -n 2 /tmp/orbit_ssh.log | sed 's/^/        /'
    [ $i -eq 2 ] && gcloud compute firewall-rules create fw-allow-iap-ssh-orbit --network=$(gcloud compute instances describe alloydb-client --zone=$ZONE --project=$PROJECT_ID --format="value(networkInterfaces[0].network.basename())") --allow=tcp:22 --source-ranges=35.235.240.0/20 --quiet >/dev/null 2>&1
    sleep 15
  done
  return 1
}

orbit_prep() {
  o_step "Auto-detecting project, zone and AlloyDB region"
  orbit_env || { o_bad "Could not detect project, zone or region. Is the lab fully provisioned?"; return 1; }
  o_info "Project: $PROJECT_ID | Zone: $ZONE | Region: $REGION"
  o_step "Waiting for lab-instance to be READY"
  orbit_wait_ready || { o_bad "lab-instance is not READY after 10 minutes."; return 1; }
  ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(ipAddress)" 2>/dev/null)
  [ -n "$ALLOYDB_IP" ] || { o_bad "No private IP found for lab-instance."; return 1; }
  o_ok "lab-instance READY | Private IP $ALLOYDB_IP"
}

orbit_stage1() {
  orbit_prep || return 1

  o_step "Connecting to alloydb-client and storing the IP in alloydbip.txt"
  orbit_remote ssh "echo $ALLOYDB_IP > alloydbip.txt; echo ORBIT_SSH_OK; command -v pgbench psql; true" || { o_bad "Cannot SSH to alloydb-client."; return 1; }
  grep -q ORBIT_SSH_OK /tmp/orbit_ssh.log && o_ok "SSH works and alloydbip.txt is written" || { o_bad "SSH check failed."; return 1; }
  grep -q pgbench /tmp/orbit_ssh.log && o_ok "pgbench and psql found on the VM" || o_warn "pgbench not found in PATH on the VM"

  printf '%s\n' "SELECT count(*) FROM pgbench_accounts;" > orbit_count.sql
  orbit_remote scp "orbit_count.sql" || { o_bad "Could not copy orbit_count.sql"; return 1; }
  orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -t -A -f orbit_count.sql; true"
  CNT=$(grep -E '^[0-9]+

 /tmp/orbit_ssh.log | tail -n 1)

  if [ "$CNT" = "50000000" ]; then
    o_ok "pgbench_accounts already has 50,000,000 rows, skipping generation"
  else
    o_step "Generating pgbench data (scale 500, background job on the VM)"
    orbit_remote ssh "pgrep -x pgbench >/dev/null && echo ORBIT_RUNNING; true"
    if grep -q ORBIT_RUNNING /tmp/orbit_ssh.log; then
      o_info "pgbench is already running on the VM, waiting for it to finish"
    else
      orbit_remote ssh "nohup sh -c 'PGPASSWORD=Change3Me pgbench -h $ALLOYDB_IP -U postgres -i -s 500 -F 90 -n postgres; echo ORBIT_EXIT=\$?' > pgbench_init.log 2>&1 < /dev/null & sleep 2; echo ORBIT_STARTED" || { o_bad "Could not start pgbench."; return 1; }
      o_ok "pgbench started in the background"
    fi
    for i in $(seq 1 60); do
      sleep 20
      orbit_remote ssh "tail -n 1 pgbench_init.log; grep -o 'ORBIT_EXIT=[0-9]*' pgbench_init.log; true" || { o_warn "poll $i failed, retrying"; continue; }
      P=$(grep -E "tuples|primary keys|vacuum|done in" /tmp/orbit_ssh.log | tail -n 1)
      grep -q 'ORBIT_EXIT=0' /tmp/orbit_ssh.log && { o_ok "pgbench finished"; break; }
      grep -q 'ORBIT_EXIT=[1-9]' /tmp/orbit_ssh.log && { o_bad "pgbench failed: $P"; return 1; }
      o_info "[$i of 60] ${P:-working...}"
    done
    orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -t -A -f orbit_count.sql; true"
    CNT=$(grep -E '^[0-9]+

 /tmp/orbit_ssh.log | tail -n 1)
  fi

  o_step "Verifying the row count"
  if [ "$CNT" = "50000000" ]; then o_ok "pgbench_accounts rows: 50,000,000"; else o_bad "pgbench_accounts rows: ${CNT:-none}. Re-run this block to resume."; return 1; fi
  sleep 10
  echo
  echo "${MAGENTA}${BOLD}>> Click 'Check my progress' for Task 1, then run Command 2 of 3.${RESET}"
}
orbit_stage1

RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; BLUE=

\033[1;34m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'; WHITE=

\033[1;37m'; ORANGE=

\033[38;5;208m'
set +H
echo "${MAGENTA}${BOLD}[ ORBIT OF OPS ]${RESET} ${ORANGE}${BOLD}Stage 2 of 3${RESET} ${WHITE}::${RESET} ${GREEN}${BOLD}Baseline, Flag and Extension (Tasks 2-4)${RESET}"

o_step() { echo; echo "${CYAN}${BOLD}▶ $*${RESET}"; }
o_ok()   { echo "  ${GREEN}${BOLD}[ OK ]${RESET} ${GREEN}$*${RESET}"; }
o_info() { echo "  ${BLUE}${BOLD}[INFO]${RESET} ${WHITE}$*${RESET}"; }
o_warn() { echo "  ${YELLOW}${BOLD}[WARN]${RESET} ${YELLOW}$*${RESET}"; }
o_bad()  { echo "  ${RED}${BOLD}[FAIL]${RESET} ${RED}$*${RESET}"; }

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  REGION=$(gcloud alloydb clusters list --project=$PROJECT_ID --filter="name~lab-cluster" --format="value(name)" 2>/dev/null | sed -n 's#.*/locations/\([^/]*\)/clusters/.*#\1#p' | head -n 1)
  [ -z "$REGION" ] && REGION=${ZONE%-*}
  [ -n "$PROJECT_ID" ] && [ -n "$ZONE" ] && [ -n "$REGION" ]
}

orbit_wait_ready() {
  local i S
  for i in $(seq 1 40); do
    S=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(state)" 2>/dev/null)
    [ "$S" = "READY" ] && return 0
    o_info "lab-instance state: ${S:-unknown} (check $i of 40)"; sleep 15
  done
  return 1
}

orbit_remote() {
  local MODE=$1 ARG=$2 i X=""
  for i in 1 2 3; do
    [ $i -eq 3 ] && X="--tunnel-through-iap"
    if [ "$MODE" = "scp" ]; then
      timeout 300 gcloud compute scp $ARG alloydb-client:~ --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X >/tmp/orbit_ssh.log 2>&1 && return 0
    else
      timeout 1500 gcloud compute ssh alloydb-client --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X --command="$ARG" >/tmp/orbit_ssh.log 2>&1 && return 0
    fi
    o_warn "SSH/SCP attempt $i of 3 failed"; tail -n 2 /tmp/orbit_ssh.log | sed 's/^/        /'
    [ $i -eq 2 ] && gcloud compute firewall-rules create fw-allow-iap-ssh-orbit --network=$(gcloud compute instances describe alloydb-client --zone=$ZONE --project=$PROJECT_ID --format="value(networkInterfaces[0].network.basename())") --allow=tcp:22 --source-ranges=35.235.240.0/20 --quiet >/dev/null 2>&1
    sleep 15
  done
  return 1
}

orbit_prep() {
  o_step "Auto-detecting project, zone and AlloyDB region"
  orbit_env || { o_bad "Could not detect project, zone or region. Is the lab fully provisioned?"; return 1; }
  o_info "Project: $PROJECT_ID | Zone: $ZONE | Region: $REGION"
  o_step "Waiting for lab-instance to be READY"
  orbit_wait_ready || { o_bad "lab-instance is not READY after 10 minutes."; return 1; }
  ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(ipAddress)" 2>/dev/null)
  [ -n "$ALLOYDB_IP" ] || { o_bad "No private IP found for lab-instance."; return 1; }
  o_ok "lab-instance READY | Private IP $ALLOYDB_IP"
}

orbit_stage2() {
  orbit_prep || return 1

  printf '%s\n' '\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;" > orbit_baseline.sql
  printf '%s\n' "SHOW google_columnar_engine.enabled;" > orbit_flag.sql
  printf '%s\n' '\c postgres' "CREATE EXTENSION IF NOT EXISTS google_columnar_engine;" '\dx' > orbit_ext.sql
  orbit_remote scp "orbit_baseline.sql orbit_flag.sql orbit_ext.sql" || { o_bad "Could not copy the SQL files to alloydb-client."; return 1; }

  o_step "Task 2: baseline test (Columnar Engine not used yet, takes about 30 s)"
  orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -f orbit_baseline.sql; true" || { o_bad "Baseline query could not run."; return 1; }
  grep -E "^Time:|Planning Time|Execution Time" /tmp/orbit_ssh.log | while read -r L; do o_info "$L"; done
  grep -qi "columnar scan" /tmp/orbit_ssh.log && o_warn "Columnar scan already active, so these numbers are not a true baseline"
  BASE_MS=$(grep "Execution Time" /tmp/orbit_ssh.log | tail -n 1 | awk '{print $3}')
  if [ -n "$BASE_MS" ]; then echo "$BASE_MS" > ~/.orbit_baseline_ms; o_ok "Baseline execution time saved: ${BASE_MS} ms"; else o_warn "Could not read the baseline execution time."; fi

  o_step "Task 3: verifying the google_columnar_engine.enabled flag"
  orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -t -A -f orbit_flag.sql; true"
  FLAG_DB=$(grep -x -E 'on|off' /tmp/orbit_ssh.log | tail -n 1)
  FLAG_API=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format=json 2>/dev/null | python3 -c "import sys,json; print(json.load(sys.stdin).get('databaseFlags',{}).get('google_columnar_engine.enabled',''))" 2>/dev/null)
  o_info "Instance setting: ${FLAG_API:-not set} | Value inside the database: ${FLAG_DB:-unknown}"
  if [ "$FLAG_DB" = "on" ]; then
    o_ok "google_columnar_engine.enabled is ON"
  else
    o_warn "Flag is not on, enabling it now (merged with existing flags, instance restarts, 3-8 min)"
    FLAGS=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format=json 2>/dev/null | python3 -c "import sys,json; d=json.load(sys.stdin).get('databaseFlags',{}); d['google_columnar_engine.enabled']='on'; print(','.join(k+'='+str(v) for k,v in d.items()))")
    gcloud alloydb instances update lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --database-flags="$FLAGS" --quiet >/tmp/orbit_upd.log 2>&1 && o_ok "Flag update applied" || { o_bad "Flag update failed"; tail -n 4 /tmp/orbit_upd.log | sed 's/^/        /'; return 1; }
    orbit_wait_ready || { o_bad "lab-instance did not return to READY."; return 1; }
    sleep 30
    orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -t -A -f orbit_flag.sql; true"
    FLAG_DB=$(grep -x -E 'on|off' /tmp/orbit_ssh.log | tail -n 1)
    [ "$FLAG_DB" = "on" ] && o_ok "google_columnar_engine.enabled is now ON" || { o_bad "Flag still reports '${FLAG_DB:-unknown}'"; return 1; }
  fi

  o_step "Task 4: Columnar Engine extension"
  orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -f orbit_ext.sql; true" || { o_bad "Extension step could not run."; return 1; }
  if grep -qE "^ *google_columnar_engine +\|" /tmp/orbit_ssh.log; then
    o_ok "$(grep -E '^ *google_columnar_engine +\|' /tmp/orbit_ssh.log | head -n 1 | tr -s ' ')"
  else
    o_bad "google_columnar_engine is not listed"; tail -n 4 /tmp/orbit_ssh.log | sed 's/^/        /'; return 1
  fi
  sleep 10
  echo
  echo "${MAGENTA}${BOLD}>> Tasks 2-4 done. Run Command 3 of 3 for Task 5.${RESET}"
}
orbit_stage2

RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; BLUE=

\033[1;34m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'; WHITE=

\033[1;37m'; ORANGE=

\033[38;5;208m'
set +H
echo "${MAGENTA}${BOLD}[ ORBIT OF OPS ]${RESET} ${ORANGE}${BOLD}Stage 3 of 3${RESET} ${WHITE}::${RESET} ${GREEN}${BOLD}Test the Columnar Engine (Task 5)${RESET}"

o_step() { echo; echo "${CYAN}${BOLD}▶ $*${RESET}"; }
o_ok()   { echo "  ${GREEN}${BOLD}[ OK ]${RESET} ${GREEN}$*${RESET}"; }
o_info() { echo "  ${BLUE}${BOLD}[INFO]${RESET} ${WHITE}$*${RESET}"; }
o_warn() { echo "  ${YELLOW}${BOLD}[WARN]${RESET} ${YELLOW}$*${RESET}"; }
o_bad()  { echo "  ${RED}${BOLD}[FAIL]${RESET} ${RED}$*${RESET}"; }

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  REGION=$(gcloud alloydb clusters list --project=$PROJECT_ID --filter="name~lab-cluster" --format="value(name)" 2>/dev/null | sed -n 's#.*/locations/\([^/]*\)/clusters/.*#\1#p' | head -n 1)
  [ -z "$REGION" ] && REGION=${ZONE%-*}
  [ -n "$PROJECT_ID" ] && [ -n "$ZONE" ] && [ -n "$REGION" ]
}

orbit_wait_ready() {
  local i S
  for i in $(seq 1 40); do
    S=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(state)" 2>/dev/null)
    [ "$S" = "READY" ] && return 0
    o_info "lab-instance state: ${S:-unknown} (check $i of 40)"; sleep 15
  done
  return 1
}

orbit_remote() {
  local MODE=$1 ARG=$2 i X=""
  for i in 1 2 3; do
    [ $i -eq 3 ] && X="--tunnel-through-iap"
    if [ "$MODE" = "scp" ]; then
      timeout 300 gcloud compute scp $ARG alloydb-client:~ --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X >/tmp/orbit_ssh.log 2>&1 && return 0
    else
      timeout 1500 gcloud compute ssh alloydb-client --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $X --command="$ARG" >/tmp/orbit_ssh.log 2>&1 && return 0
    fi
    o_warn "SSH/SCP attempt $i of 3 failed"; tail -n 2 /tmp/orbit_ssh.log | sed 's/^/        /'
    [ $i -eq 2 ] && gcloud compute firewall-rules create fw-allow-iap-ssh-orbit --network=$(gcloud compute instances describe alloydb-client --zone=$ZONE --project=$PROJECT_ID --format="value(networkInterfaces[0].network.basename())") --allow=tcp:22 --source-ranges=35.235.240.0/20 --quiet >/dev/null 2>&1
    sleep 15
  done
  return 1
}

orbit_prep() {
  o_step "Auto-detecting project, zone and AlloyDB region"
  orbit_env || { o_bad "Could not detect project, zone or region. Is the lab fully provisioned?"; return 1; }
  o_info "Project: $PROJECT_ID | Zone: $ZONE | Region: $REGION"
  o_step "Waiting for lab-instance to be READY"
  orbit_wait_ready || { o_bad "lab-instance is not READY after 10 minutes."; return 1; }
  ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(ipAddress)" 2>/dev/null)
  [ -n "$ALLOYDB_IP" ] || { o_bad "No private IP found for lab-instance."; return 1; }
  o_ok "lab-instance READY | Private IP $ALLOYDB_IP"
}

orbit_stage3() {
  orbit_prep || return 1

  printf '%s\n' '\timing on' "EXPLAIN (ANALYZE,COSTS,SETTINGS,BUFFERS,TIMING,SUMMARY,WAL,VERBOSE) SELECT count(*) FROM pgbench_accounts WHERE bid < 189 OR abalance > 100;" > orbit_plan.sql
  printf '%s\n' "SELECT google_columnar_engine_add('pgbench_accounts');" > orbit_add.sql
  orbit_remote scp "orbit_plan.sql orbit_add.sql" || { o_bad "Could not copy the SQL files to alloydb-client."; return 1; }

  o_step "Checking whether pgbench_accounts is already in the columnar store"
  orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -f orbit_plan.sql; true" || { o_bad "Could not run the explain plan."; return 1; }
  if grep -qi "columnar scan" /tmp/orbit_ssh.log; then
    o_info "Columnar scan already active, skipping the add"
  else
    o_step "Task 5: adding pgbench_accounts to the Columnar Engine"
    orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -t -A -f orbit_add.sql; true" || { o_bad "Could not run the add command."; return 1; }
    grep -qi "error" /tmp/orbit_ssh.log && { o_bad "google_columnar_engine_add reported an error"; tail -n 4 /tmp/orbit_ssh.log | sed 's/^/        /'; return 1; }
    o_ok "google_columnar_engine_add submitted"
  fi

  o_step "Re-running the explain plan until the columnar scan shows up (max about 3 min)"
  for i in $(seq 1 12); do
    orbit_remote ssh "PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -d postgres -X -q -f orbit_plan.sql; true" || { o_warn "plan run $i failed, retrying"; continue; }
    grep -qi "columnar scan" /tmp/orbit_ssh.log && { o_ok "Columnar scan is in the plan"; break; }
    o_info "[$i of 12] columnar scan not in the plan yet, waiting 15 s"; sleep 15
  done

  o_step "Result"
  grep -E "Rows Removed by Columnar Filter|Rows Aggregated by Columnar Scan|Columnar cache search mode|Planning Time|Execution Time" /tmp/orbit_ssh.log | while read -r L; do o_info "$L"; done
  NOW_MS=$(grep "Execution Time" /tmp/orbit_ssh.log | tail -n 1 | awk '{print $3}')
  BASE_MS=$(cat ~/.orbit_baseline_ms 2>/dev/null)
  if grep -qi "columnar scan" /tmp/orbit_ssh.log && [ -n "$NOW_MS" ]; then
    if [ -n "$BASE_MS" ]; then
      SPEED=$(awk -v a="$BASE_MS" -v b="$NOW_MS" 'BEGIN{ if (b>0) printf "%.0f", a/b; else print "n/a" }')
      o_ok "Baseline ${BASE_MS} ms  ->  Columnar ${NOW_MS} ms  (about ${SPEED}x faster)"
    else
      o_ok "Columnar execution time: ${NOW_MS} ms"
    fi
  else
    o_bad "Columnar scan did not appear in the plan. Wait 2 minutes and re-run this block."; return 1
  fi
  sleep 15
  echo
  echo "${MAGENTA}${BOLD}>> Click 'Check my progress' for 'Testing the Columnar Engine'.${RESET}"
  echo "${ORANGE}${BOLD}Congratulations. GSP1087 is complete - brought to you by Orbit of Ops.${RESET}"
}
orbit_stage3

# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videos