Predictive Maintenance SQL Performance Study

This project simulates a time-series sensor workload (~600k+ rows) in PostgreSQL and evaluates how query selectivity affects execution plans.

The goal is to demonstrate:

• Composite index design
• Cost-based optimization
• Sequential vs Index Scan behavior
• Performance benchmarking using EXPLAIN ANALYZE

Project Structure

predictive-maintenance-sql
│
├── python
│   └── load_readings.py
│
├── sql
│   ├── 000_reset.sql
│   ├── 001_schema.sql
│   ├── 002_seed.sql
│   ├── 003_queries.sql
│   ├── 004_indexes.sql
│   ├── 005_explain.sql
│   ├── 005b_explain_range.sql
│   └── 005c_explain_fixed.sql
│
├── run_all.ps1
└── README.md

Dataset

• Table: sensor_readings
• Rows: ~600,005
• Composite index: (sensor_id, ts)
• Workload: time-range aggregate queries on a single sensor

How to Run:

Option A (Recommended – Windows PowerShell):

.\run_all.ps1

Option B (Manual Setup):

createdb predictive_maintenance

psql -d predictive_maintenance -f sql/001_schema.sql psql -d predictive_maintenance -f sql/002_seed.sql psql -d predictive_maintenance -f sql/004_indexes.sql

python -u python/load_readings.py

psql -d predictive_maintenance -f sql/005c_explain_fixed.sql

Performance Experiment

Case 1 — Broad Time Filter (Low Selectivity)

Query:

SELECT AVG(value) FROM sensor_readings WHERE sensor_id = 1 AND ts >= NOW() - INTERVAL '10 minutes';

Execution plan:

Parallel Sequential Scan

Execution time:

• 143 ms

Explanation:

When a query matches a large portion of the table, PostgreSQL estimates that scanning the table sequentially is cheaper than performing many index lookups. The planner therefore chooses a Parallel Sequential Scan, even though an index exists.

Case 2 — Narrow Time Filter (High Selectivity)

Query:

SELECT AVG(value) FROM sensor_readings WHERE sensor_id = 1 AND ts BETWEEN <fixed_timestamp - 30 seconds> AND <fixed_timestamp>;

Execution plan with index:

Index Scan using idx_sensor_readings_sensor_ts

Execution time:

• 0.067 ms

Execution plan without index:

Parallel Sequential Scan

Execution time:

• 143 ms

This demonstrates a ~2100× performance improvement when the query predicate is highly selective.

Key Takeaways

• Index presence alone does not guarantee index usage.

• Query selectivity determines planner behavior.

• Broad analytical filters often favor Parallel Seq Scans.

• Highly selective predicates benefit significantly from composite indexing.

• PostgreSQL dynamically chooses the lowest estimated execution cost.

Reproducibility

Execution times may vary depending on:

• Hardware

• PostgreSQL configuration

• Cache state

• However, the planner behavior (Seq Scan vs Index Scan) should remain consistent given similar dataset size and query selectivity.

Author

Rosaliz García Computer Science – Data Science Track