99 commands · 6 cheat sheets · SQL
Performance and Planning master quick reference
Browse 99 commands from 6 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
Performance and Planning · 15 commands
PostgreSQL Indexes and Query Plans Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a B-tree index | CREATE INDEX orders_customer_idx
ON orders (customer_id); | View examples |
| Enforce uniqueness | CREATE UNIQUE INDEX users_email_uidx ON users (email); | View examples |
| Index several columns | CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC); | View examples |
| Include payload columns | CREATE INDEX orders_status_idx
ON orders (status) INCLUDE (total); | View examples |
| Index a subset | CREATE INDEX orders_open_idx
ON orders (created_at)
WHERE status = 'open'; | View examples |
| Index an expression | CREATE INDEX users_lower_email_idx
ON users (lower(email)); | View examples |
| Index composite values with GIN | CREATE INDEX documents_tags_idx
ON documents
USING GIN (tags); | View examples |
| Summarize ordered storage with BRIN | CREATE INDEX events_time_brin_idx
ON events
USING BRIN (occurred_at); | View examples |
| Build without blocking writes | CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (customer_id); | View examples |
| Drop with less write blocking | DROP INDEX CONCURRENTLY orders_customer_idx; | View examples |
| Inspect estimated plan | EXPLAIN SELECT * FROM orders WHERE customer_id = 42; | View examples |
| Measure execution | EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Return a machine-readable plan | EXPLAIN (FORMAT JSON)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Refresh planner statistics | ANALYZE orders; | View examples |
| List table indexes | SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'; | View examples |
Performance and Planning · 17 commands
PostgreSQL Parallel Query and JIT Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Find parallel nodes | EXPLAIN (COSTS, VERBOSE)
SELECT count(*)
FROM app.orders; | View examples |
| Measure workers launched | EXPLAIN (ANALYZE, SUMMARY)
SELECT count(*)
FROM app.orders; | View examples |
| Show worker detail | EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*)
FROM app.orders; | View examples |
| Set a local worker cap | SET LOCAL max_parallel_workers_per_gather = 4; | View examples |
| Inspect worker settings | SELECT name, setting
FROM pg_settings
WHERE name LIKE 'max_parallel%'; | View examples |
| Inspect setup cost | SHOW parallel_setup_cost; | View examples |
| Inspect table threshold | SHOW min_parallel_table_scan_size; | View examples |
| Label a function safe | ALTER FUNCTION app.net_amount(numeric, numeric) PARALLEL SAFE; | View examples |
| Label a function restricted | ALTER FUNCTION app.read_backend_state() PARALLEL RESTRICTED; | View examples |
| Disable parallel use | ALTER FUNCTION app.write_audit(text) PARALLEL UNSAFE; | View examples |
| Disable parallelism locally | SET LOCAL max_parallel_workers_per_gather = 0; | View examples |
| Check transaction isolation | SHOW transaction_isolation; | View examples |
| Enable JIT locally | SET LOCAL jit = on; | View examples |
| Inspect the JIT threshold | SHOW jit_above_cost; | View examples |
| Disable JIT locally | SET LOCAL jit = off; | View examples |
| Show JIT availability | SHOW jit_provider; | View examples |
| Track nondefault plan settings | EXPLAIN (SETTINGS, SUMMARY)
SELECT sum(total)
FROM app.orders; | View examples |
Performance and Planning · 16 commands
PostgreSQL Prepared Statements and Plan Caching Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Prepare a typed query | PREPARE order_by_id (bigint) AS
SELECT id, status, total
FROM app.orders
WHERE id = $1; | View examples |
| Execute a statement | EXECUTE order_by_id(42001); | View examples |
| Prepare a write | PREPARE add_event (uuid, text) AS
INSERT INTO app.events(account_id, kind)
VALUES ($1, $2)
RETURNING id; | View examples |
| Fix parameter meaning | PREPARE events_since (timestamptz) AS
SELECT id
FROM app.events
WHERE occurred_at >= $1::timestamptz; | View examples |
| Inspect the chosen plan | EXPLAIN (ANALYZE, BUFFERS, SETTINGS) EXECUTE orders_for_tenant(42); | View examples |
| Test custom planning | SET LOCAL plan_cache_mode = 'force_custom_plan'; | View examples |
| Test generic planning | SET LOCAL plan_cache_mode = 'force_generic_plan'; | View examples |
| Restore automatic choice | SET plan_cache_mode = 'auto'; | View examples |
| List prepared statements | SELECT name,
parameter_types,
generic_plans,
custom_plans
FROM pg_prepared_statements
ORDER BY name; | View examples |
| Find generic-plan users | SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE generic_plans > 0; | View examples |
| Discard cached plans | DISCARD PLANS; | View examples |
| Release one statement | DEALLOCATE order_by_id; | View examples |
| Release all statements | DEALLOCATE ALL; | View examples |
| Pin object resolution | PREPARE active_accounts AS
SELECT id
FROM app.accounts
WHERE disabled_at IS NULL; | View examples |
| Check server version | SHOW server_version; | View examples |
| Identify two-phase state | SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared; | View examples |
Performance and Planning · 16 commands
PostgreSQL Statistics and Cardinality Estimation Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Inspect estimated rows only | EXPLAIN SELECT * FROM app.orders WHERE status = 'open'; | View examples |
| Measure actual rows | EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM app.orders
WHERE status = 'open'; | View examples |
| Expose planning settings | EXPLAIN (ANALYZE, SETTINGS)
SELECT count(*)
FROM app.orders; | View examples |
| Analyze one table | ANALYZE app.orders; | View examples |
| Analyze selected columns | ANALYZE app.orders (tenant_id, status); | View examples |
| Inspect table statistics | SELECT *
FROM pg_stats
WHERE schemaname = 'app' AND tablename = 'orders'; | View examples |
| Check approximate table size | SELECT reltuples, relpages
FROM pg_class
WHERE oid = 'app.orders'::regclass; | View examples |
| Set a column target | ALTER TABLE app.orders ALTER COLUMN tenant_id
SET STATISTICS 500; | View examples |
| Restore the default target | ALTER TABLE app.orders ALTER COLUMN tenant_id
SET STATISTICS DEFAULT; | View examples |
| Create dependency statistics | CREATE STATISTICS app.orders_dep (dependencies)
ON tenant_id, region
FROM app.orders; | View examples |
| Create MCV statistics | CREATE STATISTICS app.orders_mcv (mcv)
ON tenant_id, status
FROM app.orders; | View examples |
| Inspect extended statistics | SELECT *
FROM pg_stats_ext
WHERE statistics_name = 'orders_tenant_status'; | View examples |
| Set a proportional distinct estimate | ALTER TABLE app.events ALTER COLUMN tenant_id
SET (n_distinct = -0.02); | View examples |
| Remove a distinct override | ALTER TABLE app.events ALTER COLUMN tenant_id RESET (n_distinct); | View examples |
| Analyze a partition hierarchy | ANALYZE app.events; | View examples |
| Inspect extended-stat data safely | SELECT statistics_name, kinds
FROM pg_stats_ext
WHERE schemaname = 'app'; | View examples |
Performance and Planning · 17 commands
PostgreSQL System Catalogs and Information Schema Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| List visible tables | SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'; | View examples |
| List PostgreSQL relations | SELECT oid::regclass, relkind
FROM pg_catalog.pg_class
WHERE relnamespace = 'app'::regnamespace; | View examples |
| Resolve the catalog schema | SELECT pg_catalog.current_schema(); | View examples |
| Resolve a relation OID | SELECT to_regclass('app.orders'); | View examples |
| Find a relation kind | SELECT relkind
FROM pg_class
WHERE oid = 'app.orders'::regclass; | View examples |
| List portable columns | SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'; | View examples |
| Render a PostgreSQL type | SELECT format_type(atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid = 'app.orders'::regclass AND attnum > 0; | View examples |
| Resolve a type safely | SELECT to_regtype('pg_catalog.int8'); | View examples |
| Show index definitions | SELECT indexrelid::regclass,
indisvalid,
pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'app.orders'::regclass; | View examples |
| Show a constraint definition | SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'orders_pkey'; | View examples |
| Check table privilege | SELECT has_table_privilege('app_reader', 'app.orders', 'SELECT'); | View examples |
| Check schema usage | SELECT has_schema_privilege('app_reader', 'app', 'USAGE'); | View examples |
| Check function execution | SELECT has_function_privilege('app_reader', 'app.total(bigint)', 'EXECUTE'); | View examples |
| Describe a catalog object | SELECT pg_describe_object('pg_class'::regclass, 'app.orders'::regclass, 0); | View examples |
| Inspect dependencies | SELECT *
FROM pg_depend
WHERE refobjid = 'app.orders'::regclass; | View examples |
| Check server version number | SELECT current_setting('server_version_num')::int; | View examples |
| Check recovery state | SELECT pg_is_in_recovery(); | View examples |
Performance and Planning · 18 commands
PostgreSQL Table Partitioning and Pruning Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a range-partitioned table | CREATE TABLE events (event_id bigint, occurred_at timestamptz NOT NULL) PARTITION BY RANGE (occurred_at); | View examples |
| Create a list-partitioned table | CREATE TABLE accounts (account_id bigint, region_code text NOT NULL) PARTITION BY LIST (region_code); | View examples |
| Create a hash-partitioned table | CREATE TABLE jobs (job_id bigint, tenant_id bigint NOT NULL) PARTITION BY HASH (tenant_id); | View examples |
| Create a monthly partition | CREATE TABLE events_2026_08 PARTITION OF events FOR
VALUES
FROM ('2026-08-01') TO ('2026-09-01'); | View examples |
| Create a default partition | CREATE TABLE events_default PARTITION OF events DEFAULT; | View examples |
| Create a list partition | CREATE TABLE accounts_americas PARTITION OF accounts FOR
VALUES IN ('BR', 'CA', 'US'); | View examples |
| Create a hash partition | CREATE TABLE jobs_h0 PARTITION OF jobs FOR
VALUES
WITH (MODULUS 4, REMAINDER 0); | View examples |
| Create matching child indexes | CREATE INDEX events_tenant_time_idx
ON events (tenant_id, occurred_at); | View examples |
| Declare partition-safe uniqueness | PRIMARY KEY (event_id, occurred_at) | View examples |
| Filter with matching range bounds | WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00' AND occurred_at < TIMESTAMPTZ '2026-09-01 00:00:00+00' | View examples |
| Inspect selected partitions | EXPLAIN (COSTS OFF)
SELECT count(*)
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'; | View examples |
| Check pruning configuration | SHOW enable_partition_pruning; | View examples |
| Build an attachable table | CREATE TABLE events_2026_09 (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS); | View examples |
| Validate the intended bound | ALTER TABLE events_2026_09 VALIDATE CONSTRAINT events_2026_09_bound; | View examples |
| Attach a prepared partition | ALTER TABLE events ATTACH PARTITION events_2026_09 FOR
VALUES
FROM ('2026-09-01') TO ('2026-10-01'); | View examples |
| List a partition hierarchy | SELECT relid, parentrelid, isleaf, level
FROM pg_partition_tree('events'::regclass); | View examples |
| Inspect partition bounds | SELECT relname, pg_get_expr(relpartbound, oid)
FROM pg_class
WHERE relispartition
ORDER BY relname; | View examples |
| Analyze the partitioned parent | ANALYZE events; | View examples |