Database Performance Tuning: Query-Level Indexing & Measurement Guide

Baseline measurements for database optimization through workload analysis

The first step is correct measurement: for PostgreSQL use pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS) and pg_buffercache; for SQL Server use Query Store and Extended Events. Example: to reset statistics in PostgreSQL, run the workload for 5 minutes and then get the top 10 heaviest queries

SELECT queryid, calls, total_time, mean_time, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

This step objectively identifies which queries should be optimized in the context of a database or SQL course.

Steps for the profiling process: (1) reset measurements (Postgres: pg_stat_statements_reset(); SQL Server: clear Query Store snapshot), (2) run the standard workload, (3) capture plans with EXPLAIN ANALYZE, (4) apply changes, (5) measure again. Example reset commands:

-- PostgreSQL
SELECT pg_stat_statements_reset();
-- SQL Server (T-SQL)
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL; -- caution: schedule in production environments

Database indexing: index usage heatmap and a live example

A heatmap gives insight: to see which index pages are heavily used, install the pg_buffercache extension in PostgreSQL and aggregate block usage. Installation and sample query:

-- Postgres: enable extension (superuser)
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
-- Example: cached page count per table
SELECT c.relname, count(*) AS cached_pages
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relname = 'orders'
GROUP BY c.relname;

This lets you quantify how much each table/index is read from memory.

For live sampling, a small Python script: every 5 seconds pull pg_stat_activity and EXPLAIN ANALYZE for the active plan, count index usage and write to CSV. This shapes your indexing strategy based on workload data. Minimal example script:

import psycopg2, time, csv
conn = psycopg2.connect('host=127.0.0.1 dbname=app user=me')
cur = conn.cursor()
with open('plan_samples.csv','w',newline='') as f:
    w = csv.writer(f)
    w.writerow(['ts','query','total_time'])
    for _ in range(12):
        cur.execute("SELECT query FROM pg_stat_activity WHERE state='active' AND pid <> pg_backend_pid() LIMIT 1")
        q = cur.fetchone()
        if q:
            cur.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + q[0])
            plan = cur.fetchone()[0]
            total = plan[0]['Plan']['Actual Total Time'] if isinstance(plan, list) else 0
            w.writerow([time.time(), q[0][:200].replace('\n',' '), total])
        time.sleep(5)

Speeding queries with partial and covering indexes: real EXPLAIN comparison

The combination of partial indexes and INCLUDE (covering) columns reduces IO and heap-fetches, lowering query times. Measure before/after with an example table and query:

-- Örnek tablo ve veri hazırlığı
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int, status text, created_at timestamptz, total numeric);
CREATE INDEX ON orders (customer_id);
-- Sorgu: aktif status olan ve created_at aralığında olan kayıtlar
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE status='paid' AND created_at > now() - interval '30 days' AND customer_id = 42;

-- Optimizasyon: partial index + covering
CREATE INDEX orders_paid_recent_idx ON orders (customer_id, created_at) INCLUDE (total) WHERE status = 'paid' AND created_at > now() - interval '90 days';
VACUUM ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE status='paid' AND created_at > now() - interval '30 days' AND customer_id = 42;

The measurement approach: compare the 'Buffers: shared hit=X read=Y' values in EXPLAIN ANALYZE output; aim to reduce shared_blks_read and heap_fetches.

Why it works — the mechanism: with a covering index an index-only scan can avoid heap fetches, but index-only scans require visibility map bits to be set. Therefore run VACUUM (or ensure autovacuum) after changes to update the visibility map. Measurement step:

-- Visibility map kontrolü
SELECT relname, heap_blks_read, heap_blks_hit FROM pg_statio_user_tables WHERE relname='orders';
-- vacuum sonrası tekrar ölçün
VACUUM VERBOSE orders;
SELECT * FROM pg_statio_user_tables WHERE relname='orders';

Index maintenance, fillfactor and plan stability (SQL Server and PostgreSQL)

Index fragmentation leads to write amplification and plan changes. Use online rebuild in SQL Server, and REINDEX CONCURRENTLY or pg_repack in PostgreSQL. SQL Server example:

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);
-- Fragmentation ölçümü
SELECT index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');

Measure fragmentation and CPU/IO impact before and after these commands.

PostgreSQL example: adjust fillfactor and use pg_repack for online rebuilds. Use iostat and EXPLAIN (ANALYZE, BUFFERS) for measurement. Commands:

-- Fillfactor değiştirme (yeni index oluşturma örneği)
CREATE INDEX CONCURRENTLY CONCURRENTLY_idx ON orders(customer_id) WITH (FILLFACTOR=70);
-- Alternatif: pg_repack (örnek işletim sistemi komutu, paket kurulu olmalı)
pg_repack --table=public.orders postgresql://user:pwd@127.0.0.1/appdb
-- IO ölçümü (Linux)
iostat -x 1 5

Measurement logic: capture iostat and EXPLAIN IO/CPU profile before rebuild, repeat the same workload after rebuild and report the delta.

For plan stability: in SQL Server you can 'force plan' from Query Store; in PostgreSQL test parameters that trigger plan changes (random_page_cost, effective_cache_size) and validate with EXPLAIN. Example:

-- Postgres test: geçici olarak random_page_cost azalt ve plan değişimini kontrol et
SET LOCAL random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- geri döndürmek için transaction dışına çık

Edge cases, common mistakes and operational checks

Common mistake: creating unnecessary indexes. Verify with measurements: after a new index, idx_scan and idx_tup_read should increase; otherwise drop the index. To see if an index is used in Postgres:

SELECT indexrelid::regclass AS index, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes WHERE schemaname='public' AND tablename='orders';
-- If idx_scan is 0 after 30 days, drop the index

NoSQL context: within a NoSQL training context, perform similar 'hot key' analysis for Redis/Elastic using slowlog or hot shard metrics. For example, for Redis:

redis-cli SLOWLOG GET 128
-- use latency top commands to see frequently called keys

This helps decide where to apply indexing/partitioning/denormalization in your data architecture.

Rollback and testing: before applying changes to production, run workload tests on staging with pgbench or HammerDB and compare EXPLAIN ANALYZE outputs and total times in pg_stat_statements. Example command:

pgbench -c 20 -j 4 -T 60 -f myscript.sql postgres
-- collect query timings to compare results

Frequently Asked Questions

Which metrics should I collect first for database optimization?

Start with priorities: total time and Buffers (hit/read) from EXPLAIN (ANALYZE, BUFFERS), total_time and calls from pg_stat_statements, and OS-level IO wait metrics via iostat or perf. PostgreSQL example:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SELECT queryid, calls, total_time FROM pg_stat_statements WHERE query LIKE '%your_query%';
iostat -x 1 3

These metrics quantify the effect of changes.

When indexing, should I prefer partial indexes or covering indexes?

The deciding factors are the query's filter conditions and returned columns. If the WHERE clause targets a fixed state, a partial index is smaller with lower write cost; if the query frequently selects a few columns and returns additional columns, an INCLUDE covering index enables index-only scans and reduces heap fetches. Verify with EXPLAIN ANALYZE and compare pg_stat_user_indexes idx_scan/idx_tup_read values.

In an SQL Server training context, how should I measure index rebuild and fillfactor effects?

In SQL Server measure fragmentation first with sys.dm_db_index_physical_stats, then run ALTER INDEX ... REBUILD WITH (ONLINE=ON, FILLFACTOR=X) and monitor IO/metrics with sys.dm_db_index_operational_stats. Also compare query time histograms from Query Store before and after. Example queries:

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);
-- Query Store snapshot comparison is performed via GUI or T-SQL