pg_hint_plan.sql

-- Define the tables

CREATE TABLE hotd (
    region_code bigint NOT NULL,
    season integer,
    episode integer,
    viewers integer,
    PRIMARY KEY(region_code, season, episode)
);

CREATE INDEX idx_season_episode_hotd ON hotd(season, episode);

CREATE TABLE rop (
    region_code bigint NOT NULL,
    season integer,
    episode integer,
    viewers integer,
    PRIMARY KEY(region_code, season, episode)
);

CREATE INDEX idx_season_episode_rop ON rop(season, episode);

-- Load the data

INSERT INTO hotd SELECT code, season, episode, floor(random()*100000000) FROM generate_series(1, 10000)code, generate_series(1, 8)season, generate_series(1, 10)episode;

INSERT INTO rop SELECT code, season, episode, floor(random()*100000000) FROM generate_series(1, 5000)code, generate_series(1, 8)season, generate_series(1, 10)episode;

-- hint phrase for the explain analyze

CREATE EXTENSION pg_hint_plan;

SET pg_hint_plan.enable_hint_table = on;

EXPLAIN ANALYZE SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND az.region_code=ds.region_code) WHERE az.season=4 and az.episode=1;

INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('EXPLAIN ANALYZE SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND az.region_code=ds.region_code) WHERE az.season=? and az.episode=?;','','HashJoin(az ds)');

-- StatementAPI with static constants

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Standalone {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.yugabyte.Driver");
		String query = "SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN "
				+ "hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND "
				+ "az.region_code=ds.region_code) WHERE az.season=4 and az.episode=1";
		try (Connection conn = DriverManager.getConnection("jdbc:yugabytedb://127.0.0.2:5433/yugabyte",
				"yugabyte", "yugabyte");
			 Statement stmt = conn.createStatement()) {
			long execTime = System.currentTimeMillis();
			stmt.execute(query);
			System.out.println("Execution time: " + (System.currentTimeMillis() - execTime));
		}
	}
}


INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND az.region_code=ds.region_code) WHERE az.season=? and az.episode=?','','HashJoin(az ds)');

-- PreparedStatementAPI with bind parameters

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Standalone {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.yugabyte.Driver");
		String query = "SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN "
				+ "hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND "
				+ "az.region_code=ds.region_code) WHERE az.season=? and az.episode=?";
		try (Connection conn = DriverManager.getConnection("jdbc:yugabytedb://127.0.0.2:5433/yugabyte",
				"yugabyte", "yugabyte");
			 PreparedStatement pstmt = conn.prepareStatement(query)) {
			pstmt.setInt(1, 4);
			pstmt.setInt(2, 1);
			long execTime = System.currentTimeMillis();
			pstmt.execute();
			System.out.println("Execution time: " + (System.currentTimeMillis() - execTime));
		}
	}
}

INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND az.region_code=ds.region_code) WHERE az.season=$1 and az.episode=$2','','HashJoin(az ds)');

-- PreparedStatementAPI with bind parameter and static constant

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Standalone {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.yugabyte.Driver");
		String query = "SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN "
				+ "hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND "
				+ "az.region_code=ds.region_code) WHERE az.season=4 and az.episode=?";
		try (Connection conn = DriverManager.getConnection("jdbc:yugabytedb://127.0.0.2:5433/yugabyte",
				"yugabyte", "yugabyte");
			 PreparedStatement pstmt = conn.prepareStatement(query)) {
			pstmt.setInt(1, 1);
			long execTime = System.currentTimeMillis();
			pstmt.execute();
			System.out.println("Execution time: " + (System.currentTimeMillis() - execTime));
		}
	}
}

INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND az.region_code=ds.region_code) WHERE az.season=? and az.episode=$1','','HashJoin(az ds)');

-- With special character
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Standalone {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.yugabyte.Driver");
		String query = "SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN" + "\n"
				+ "hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND" + "\n"
				+ "az.region_code=ds.region_code) WHERE az.season=4 and az.episode=?";
		try (Connection conn = DriverManager.getConnection("jdbc:yugabytedb://127.0.0.2:5433/yugabyte",
				"yugabyte", "yugabyte");
			 PreparedStatement pstmt = conn.prepareStatement(query)) {
			pstmt.setInt(1, 1);
			long execTime = System.currentTimeMillis();
			pstmt.execute();
			System.out.println("Execution time: " + (System.currentTimeMillis() - execTime));
		}
	}
}

INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('SELECT SUM(az.viewers), SUM(ds.viewers) FROM rop az JOIN'||E'\n'||'hotd ds ON (az.season=ds.season AND az.episode=ds.episode AND'||E'\n'||'az.region_code=ds.region_code) WHERE az.season=? and az.episode=$1','','HashJoin(az ds)');