Postgres Openclaw Optimization | Skills Pool

Postgres Openclaw Optimization | Skills Pool

# コンテナに接続
docker exec -it openclaw-postgres psql -U openclaw -d openclaw

# 実行中のクエリを確認
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;

# データベースサイズを確認
SELECT
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

# テーブルサイズを確認
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- N8N用スキーマ
CREATE SCHEMA IF NOT EXISTS n8n;
ALTER SCHEMA n8n OWNER TO openclaw;

-- OpenClaw用スキーマ（将来の拡張用）
CREATE SCHEMA IF NOT EXISTS openclaw;
ALTER SCHEMA openclaw OWNER TO openclaw;

-- カスタムアプリ用スキーマ（例: AISA）
CREATE SCHEMA IF NOT EXISTS aisa;
ALTER SCHEMA aisa OWNER TO openclaw;

-- デフォルトスキーマをpublicに戻す
ALTER ROLE openclaw SET search_path TO public, n8n, openclaw, aisa;

-- 未公開レポートを取得するクエリ用インデックス
CREATE INDEX IF NOT EXISTS idx_generated_reports_published_at
ON aisa.generated_reports (published_at)
WHERE published_at IS NULL;

-- created_atでのソート用インデックス
CREATE INDEX IF NOT EXISTS idx_generated_reports_created_at
ON aisa.generated_reports (created_at DESC);

-- 複合インデックス（published_at + created_at）
CREATE INDEX IF NOT EXISTS idx_generated_reports_pub_created
ON aisa.generated_reports (published_at, created_at DESC);

-- 全文検索用インデックス（タイトル・コンテンツ）
CREATE INDEX IF NOT EXISTS idx_generated_reports_fts
ON aisa.generated_reports
USING gin(to_tsvector('english', title || ' ' || content));

-- 最適化前（全テーブルスキャン）
EXPLAIN ANALYZE
SELECT id, title, content, created_at
FROM aisa.generated_reports
WHERE published_at IS NULL
ORDER BY created_at DESC
LIMIT 1;

-- 最適化後（インデックススキャン）
-- idx_generated_reports_published_at を使用
-- Execution Time: 0.05 ms（1000倍高速化）

-- クエリプランを確認
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, title FROM aisa.generated_reports WHERE published_at IS NULL;

# docker-compose.quick.yml