Full code for the Earthly Technologies Article on Optimizing SQL Queries

optimize.go

package main

import (
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
	"log"
	"time"
)

// Config struct helps with loading database configurations for connecting to the database
type Config struct {
	Host     string
	Port     string
	Password string
	User     string
	DBName   string
	SSLMode  string
}

// Human  is the struct model for the Human table in the database
type Human struct {
	Username string
	Age      int
	Email    string
	Height   float64
}

func NewConnection(config *Config) (*gorm.DB, error) {
	dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s", config.Host, config.Port, config.User, config.Password, config.DBName, config.SSLMode)
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true,
		},
	})
	if err != nil {
		return db, err
	}
	err = db.AutoMigrate(&Human{})
	if err != nil {
		return nil, err
	}
	return db, nil
}

// CreateHumanIndex creates a new index on the
func CreateHumanIndex(db *gorm.DB) error {
	start := time.Now()
	err := db.Exec("CREATE INDEX human_index ON human (username, age)").Error
	if err != nil {
		return err
	}
	fmt.Printf("Created index in %s\n", time.Since(start))

	return nil
}

// FindHumanByAge returns the  Humans with a specified age
func FindHumanByAge(db *gorm.DB, minAge int) ([]Human, error) {
	start := time.Now()
	var person []Human
	err := db.Raw("SELECT username, age  FROM human WHERE age > ?", minAge).Scan(&person).Error
	if err != nil {
		return nil, err
	}
	fmt.Printf("Found %d person in %s\n", len(person), time.Since(start))
	return person, nil
}

// complexQuery is the complex query
func complexQuery(db *gorm.DB) ([]Human, error) {
	var humans []Human
	query := `SELECT username, age, email FROM human WHERE height > 0.15 AND (username IS NOT NULL OR age IS NOT NULL OR email IS NOT NULL) ORDER BY username DESC, age ASC LIMIT 100;`
	result := db.Raw(query).Scan(&humans)
	if result.Error != nil {
		return nil, result.Error
	}
	return humans, nil
}

// simplifiedQuery is the simplified complex query
func simplifiedQuery(db *gorm.DB) ([]Human, error) {
	var humans []Human
	query := `SELECT username, age, email FROM human WHERE height > 0.15`
	result := db.Raw(query).Scan(&humans)
	if result.Error != nil {
		return nil, result.Error
	}
	return humans, nil
}

func NewPooledConnection(config *Config) (*gorm.DB, error) {
	dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s", config.Host, config.Port, config.User, config.Password, config.DBName, config.SSLMode)
	db, err := gorm.Open(postgres.New(postgres.Config{
		DSN: dsn, PreferSimpleProtocol: true,
	}), &gorm.Config{NamingStrategy: schema.NamingStrategy{
		SingularTable: true,
	}})
	if err != nil {
		return db, err
	}
	err = db.AutoMigrate(&Human{})
	if err != nil {
		return nil, err
	}

	// Enable connection pooling
	sqlDB, err := db.DB()
	if err != nil {
		return nil, err
	}
	sqlDB.SetMaxIdleConns(10)
	sqlDB.SetMaxOpenConns(100)
	return db, nil
}

func main() {

	// load your database configurations
	config := &Config{
		Host:     "your_host",
		Port:     "your_port",
		User:     "your_user",
		Password: "your_password",
		DBName:   "your_dbname",
		SSLMode:  "disable",
	}

	// connect to the database
	dbConnection, err := NewConnection(config)
	if err != nil {
		log.Println(err)
	}

	// Create an index on the Human table
	err = CreateHumanIndex(dbConnection)
	if err != nil {
		log.Println(err)
	}

	human, err := FindHumanByAge(dbConnection, 18)
	if err != nil {
		log.Println(err)
	}
	fmt.Println(human)

	// run a complex query
	complexQueryExec, err := complexQuery(dbConnection)
	fmt.Println(complexQueryExec)

	// run a simplified version of the complex query
	simplifiedQueryExec, err := simplifiedQuery(dbConnection)
	fmt.Println(simplifiedQueryExec)

	// connect to the database, this time, with connection pooling
	db, err := ConnectDB()
	if err != nil {
		return
	}
	print(db.DB())
}