query-optimization

class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

CREATE TABLE posts (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    bigint NOT NULL REFERENCES authors (id),
    title        text   NOT NULL,
    body         text   NOT NULL,
    published_at timestamptz NOT NULL DEFAULT now()

module ExistenceChecks
  extend ActiveSupport::Concern

  class_methods do
    def has_any?(conditions = {})
      relation = conditions.present? ? where(conditions) : all

-- Basic query debugging with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Output shows query plan:
-- Seq Scan on users (cost=0.00..25.00 rows=1 width=100)
--   Filter: (email = 'test@example.com'::text)

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export async function getFeedNaive(limit = 20) {
  const posts = await prisma.post.findMany({

-- INNER JOIN: Only matching rows
SELECT
  users.name,
  orders.order_number,
  orders.total
FROM users

# In rails console
query = Post.joins(:author)
           .where(published_at: 1.week.ago..Time.current)
           .where(users: { status: 'active' })
           .order(created_at: :desc)

-- Basic EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN with cost and row estimates
-- Output shows: Seq Scan on users (cost=0.00..15.50 rows=1 width=100)

CREATE TABLE subscriptions (
    id                  BIGGENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id         BIGINT       NOT NULL,
    plan_code           TEXT         NOT NULL,
    status              TEXT         NOT NULL DEFAULT 'active',
    current_period_end  TIMESTAMPTZ  NOT NULL,