// ============================================
// Query Performance Analyzer Tool
// Complete Implementation with All Features
// ============================================
viewof queryAnalyzerTool = {
// IEEE Color palette
const colors = {
navy: "#2C3E50",
teal: "#16A085",
orange: "#E67E22",
gray: "#7F8C8D",
lightGray: "#ECF0F1",
white: "#FFFFFF",
red: "#E74C3C",
green: "#27AE60",
purple: "#9B59B6",
yellow: "#F1C40F",
blue: "#3498DB"
};
// Database configurations with detailed characteristics
const databases = {
influxdb: {
name: "InfluxDB",
color: "#22ADF6",
type: "Time-Series",
strengths: ["Time-range queries", "Aggregations", "High write throughput", "Retention policies"],
weaknesses: ["Complex joins", "Ad-hoc queries", "Non-time queries"],
optimalFor: ["Metrics", "Monitoring", "IoT sensor data"],
characteristics: {
writeSpeed: 95,
readSpeed: 85,
aggregation: 90,
joins: 10,
scalability: 85
}
},
timescaledb: {
name: "TimescaleDB",
color: "#FDB515",
type: "Time-Series (PostgreSQL)",
strengths: ["SQL compatibility", "Time-series + relational", "Compression", "Continuous aggregates"],
weaknesses: ["Complex setup", "Memory intensive", "Learning curve"],
optimalFor: ["IoT with metadata", "Analytics", "Historical analysis"],
characteristics: {
writeSpeed: 75,
readSpeed: 80,
aggregation: 85,
joins: 85,
scalability: 70
}
},
mongodb: {
name: "MongoDB",
color: "#13AA52",
type: "Document Store",
strengths: ["Flexible schema", "Horizontal scaling", "JSON queries", "Aggregation pipeline"],
weaknesses: ["Memory usage", "Join performance", "Consistency tradeoffs"],
optimalFor: ["Event logs", "Device metadata", "Unstructured data"],
characteristics: {
writeSpeed: 80,
readSpeed: 75,
aggregation: 70,
joins: 40,
scalability: 90
}
},
postgresql: {
name: "PostgreSQL",
color: "#336791",
type: "Relational",
strengths: ["ACID compliance", "Complex queries", "JSON support", "Extensions"],
weaknesses: ["Vertical scaling", "Time-series performance", "Sharding complexity"],
optimalFor: ["Transactional data", "Complex queries", "Mixed workloads"],
characteristics: {
writeSpeed: 70,
readSpeed: 80,
aggregation: 75,
joins: 95,
scalability: 50
}
},
cassandra: {
name: "Cassandra",
color: "#1287B1",
type: "Wide-Column",
strengths: ["Linear scaling", "High availability", "Write performance", "Geographic distribution"],
weaknesses: ["Read latency", "No joins", "Query flexibility", "Learning curve"],
optimalFor: ["Write-heavy workloads", "Geographic distribution", "High availability"],
characteristics: {
writeSpeed: 95,
readSpeed: 65,
aggregation: 50,
joins: 5,
scalability: 98
}
}
};
// Query types with detailed examples
const queryTypes = {
time_range: {
name: "Time-Range Aggregation",
description: "Aggregate sensor data over a time window",
examples: {
influxdb: `SELECT mean("temperature"), max("humidity")
FROM "sensors"
WHERE time > now() - 1h
GROUP BY time(5m), "device_id"`,
timescaledb: `SELECT time_bucket('5 minutes', timestamp) AS bucket,
device_id,
AVG(temperature), MAX(humidity)
FROM sensors
WHERE timestamp > NOW() - INTERVAL '1 hour'
GROUP BY bucket, device_id`,
mongodb: `db.sensors.aggregate([
{ $match: { timestamp: { $gte: new Date(Date.now() - 3600000) } } },
{ $group: {
_id: { device: "$device_id", bucket: { $dateTrunc: { date: "$timestamp", unit: "minute", binSize: 5 } } },
avgTemp: { $avg: "$temperature" },
maxHumidity: { $max: "$humidity" }
}}
])`,
postgresql: `SELECT date_trunc('minute', timestamp) AS bucket,
device_id,
AVG(temperature), MAX(humidity)
FROM sensors
WHERE timestamp > NOW() - INTERVAL '1 hour'
GROUP BY bucket, device_id`,
cassandra: `SELECT device_id,
toTimestamp(toDate(timestamp)) AS day,
avg(temperature), max(humidity)
FROM sensors
WHERE device_id = 'sensor-001'
AND timestamp > '2024-01-01'
GROUP BY device_id, toDate(timestamp)`
}
},
latest_value: {
name: "Latest Value Query",
description: "Get the most recent reading for each device",
examples: {
influxdb: `SELECT last("temperature"), last("humidity")
FROM "sensors"
GROUP BY "device_id"`,
timescaledb: `SELECT DISTINCT ON (device_id)
device_id, timestamp, temperature, humidity
FROM sensors
ORDER BY device_id, timestamp DESC`,
mongodb: `db.sensors.aggregate([
{ $sort: { device_id: 1, timestamp: -1 } },
{ $group: {
_id: "$device_id",
latest: { $first: "$$ROOT" }
}}
])`,
postgresql: `SELECT DISTINCT ON (device_id)
device_id, timestamp, temperature, humidity
FROM sensors
ORDER BY device_id, timestamp DESC`,
cassandra: `SELECT device_id, temperature, humidity
FROM sensors_latest
WHERE device_id IN ('sensor-001', 'sensor-002')`
}
},
anomaly_detection: {
name: "Anomaly Detection Query",
description: "Find readings outside normal thresholds",
examples: {
influxdb: `SELECT *
FROM "sensors"
WHERE temperature > 80 OR temperature < 10
AND time > now() - 24h`,
timescaledb: `WITH stats AS (
SELECT device_id,
AVG(temperature) AS avg_temp,
STDDEV(temperature) AS std_temp
FROM sensors
WHERE timestamp > NOW() - INTERVAL '7 days'
GROUP BY device_id
)
SELECT s.*, 'anomaly' AS status
FROM sensors s
JOIN stats st ON s.device_id = st.device_id
WHERE s.timestamp > NOW() - INTERVAL '1 hour'
AND ABS(s.temperature - st.avg_temp) > 2 * st.std_temp`,
mongodb: `db.sensors.aggregate([
{ $lookup: {
from: "device_thresholds",
localField: "device_id",
foreignField: "_id",
as: "thresholds"
}},
{ $match: {
$expr: { $or: [
{ $gt: ["$temperature", { $first: "$thresholds.max_temp" }] },
{ $lt: ["$temperature", { $first: "$thresholds.min_temp" }] }
]}
}}
])`,
postgresql: `SELECT s.*, 'anomaly' AS status
FROM sensors s
JOIN device_thresholds t ON s.device_id = t.device_id
WHERE s.timestamp > NOW() - INTERVAL '1 hour'
AND (s.temperature > t.max_temp OR s.temperature < t.min_temp)`,
cassandra: `SELECT * FROM sensors
WHERE device_id = 'sensor-001'
AND timestamp > '2024-01-01'
AND temperature > 80
ALLOW FILTERING`
}
},
join_operation: {
name: "Join with Metadata",
description: "Combine sensor data with device information",
examples: {
influxdb: `-- InfluxDB: Not recommended
-- Use Flux or embed metadata in tags
from(bucket: "iot")
|> range(start: -1h)
|> filter(fn: (r) => r._measurement == "sensors")
|> group(columns: ["device_id"])
|> last()`,
timescaledb: `SELECT s.timestamp, s.temperature, s.humidity,
d.location, d.device_type, d.firmware_version
FROM sensors s
JOIN devices d ON s.device_id = d.id
WHERE s.timestamp > NOW() - INTERVAL '1 hour'
ORDER BY s.timestamp DESC`,
mongodb: `db.sensors.aggregate([
{ $match: { timestamp: { $gte: new Date(Date.now() - 3600000) } } },
{ $lookup: {
from: "devices",
localField: "device_id",
foreignField: "_id",
as: "device"
}},
{ $unwind: "$device" },
{ $project: {
timestamp: 1, temperature: 1, humidity: 1,
location: "$device.location",
deviceType: "$device.type"
}}
])`,
postgresql: `SELECT s.timestamp, s.temperature, s.humidity,
d.location, d.device_type, d.firmware_version
FROM sensors s
INNER JOIN devices d ON s.device_id = d.id
WHERE s.timestamp > NOW() - INTERVAL '1 hour'
ORDER BY s.timestamp DESC`,
cassandra: `-- Cassandra: Denormalize data
-- Embed device info in sensor table
SELECT device_id, timestamp, temperature,
location, device_type
FROM sensors_with_metadata
WHERE device_id = 'sensor-001'
AND timestamp > '2024-01-01'`
}
},
rollup: {
name: "Time-Series Rollup",
description: "Downsample data to hourly/daily granularity",
examples: {
influxdb: `CREATE CONTINUOUS QUERY "hourly_mean"
ON "iot_db"
BEGIN
SELECT mean("temperature") AS "avg_temperature",
max("temperature") AS "max_temperature"
INTO "sensors_hourly"
FROM "sensors"
GROUP BY time(1h), "device_id"
END`,
timescaledb: `CREATE MATERIALIZED VIEW sensors_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', timestamp) AS bucket,
device_id,
AVG(temperature) AS avg_temp,
MIN(temperature) AS min_temp,
MAX(temperature) AS max_temp,
COUNT(*) AS sample_count
FROM sensors
GROUP BY bucket, device_id
WITH NO DATA;
SELECT add_continuous_aggregate_policy('sensors_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');`,
mongodb: `db.sensors.aggregate([
{ $group: {
_id: {
device: "$device_id",
hour: { $dateTrunc: { date: "$timestamp", unit: "hour" } }
},
avgTemp: { $avg: "$temperature" },
minTemp: { $min: "$temperature" },
maxTemp: { $max: "$temperature" },
count: { $sum: 1 }
}},
{ $out: "sensors_hourly" }
])`,
postgresql: `CREATE MATERIALIZED VIEW sensors_hourly AS
SELECT date_trunc('hour', timestamp) AS hour,
device_id,
AVG(temperature) AS avg_temp,
MIN(temperature) AS min_temp,
MAX(temperature) AS max_temp,
COUNT(*) AS sample_count
FROM sensors
GROUP BY hour, device_id;
CREATE UNIQUE INDEX ON sensors_hourly (hour, device_id);`,
cassandra: `-- Pre-aggregate during write
-- Use separate rollup table
INSERT INTO sensors_hourly (device_id, hour, avg_temp, min_temp, max_temp, count)
VALUES ('sensor-001', '2024-01-01 10:00:00', 25.5, 24.0, 27.0, 720)
USING TTL 2592000`
}
}
};
// Performance calculation with detailed metrics
function calculatePerformance(db, queryType, tableSize, hasIndex, concurrentQueries) {
const baseTimes = {
influxdb: { time_range: 15, latest_value: 8, anomaly_detection: 25, join_operation: 500, rollup: 30 },
timescaledb: { time_range: 25, latest_value: 12, anomaly_detection: 35, join_operation: 45, rollup: 40 },
mongodb: { time_range: 40, latest_value: 20, anomaly_detection: 60, join_operation: 150, rollup: 80 },
postgresql: { time_range: 35, latest_value: 10, anomaly_detection: 40, join_operation: 30, rollup: 120 },
cassandra: { time_range: 50, latest_value: 5, anomaly_detection: 80, join_operation: 1000, rollup: 100 }
};
const sizeMultiplier = Math.log10(tableSize) / 4;
const indexMultiplier = hasIndex ? 0.25 : 3.0;
const concurrencyMultiplier = 1 + (concurrentQueries - 1) * 0.15;
let baseTime = baseTimes[db][queryType] || 50;
let estimatedTime = baseTime * sizeMultiplier * indexMultiplier * concurrencyMultiplier;
// Calculate rows scanned based on query type
let rowsScanned;
if (queryType === "latest_value") {
rowsScanned = hasIndex ? Math.min(tableSize * 0.001, 1000) : Math.min(tableSize * 0.1, 100000);
} else if (queryType === "time_range" || queryType === "rollup") {
rowsScanned = hasIndex ? Math.min(tableSize * 0.02, 50000) : Math.min(tableSize * 0.2, 500000);
} else if (queryType === "anomaly_detection") {
rowsScanned = hasIndex ? Math.min(tableSize * 0.05, 100000) : Math.min(tableSize * 0.3, 800000);
} else {
rowsScanned = Math.min(tableSize * 0.15, 300000);
}
// Memory estimation
const memoryMB = (rowsScanned * 0.0001) + (queryType === "join_operation" ? 50 : 10);
// Cost breakdown (relative percentages)
const dbOverhead = db === "cassandra" ? 25 : db === "mongodb" ? 20 : 15;
const totalUnits = 100;
let cpuPct, ioPct, networkPct, memPct;
if (hasIndex) {
cpuPct = 35;
ioPct = 30;
networkPct = 15;
memPct = 20;
} else {
cpuPct = 25;
ioPct = 50;
networkPct = 10;
memPct = 15;
}
// Throughput calculation
const baseQPS = {
influxdb: 5000,
timescaledb: 3000,
mongodb: 4000,
postgresql: 2500,
cassandra: 8000
};
const qps = Math.round(baseQPS[db] / (estimatedTime / 10) / concurrencyMultiplier);
return {
estimatedTimeMs: Math.round(estimatedTime),
rowsScanned: Math.round(rowsScanned),
memoryMB: Math.round(memoryMB),
indexUsed: hasIndex,
cpuCost: cpuPct,
ioCost: ioPct,
networkCost: networkPct,
memoryCost: memPct,
throughputQPS: qps,
concurrentQueries
};
}
// Generate execution plan steps
function generateExecutionPlan(db, queryType, hasIndex, tableSize) {
const plans = {
time_range: hasIndex ? [
{ op: db === "timescaledb" ? "Chunk Scan" : "Index Range Scan", cost: 25, rows: Math.round(tableSize * 0.02), desc: "Scan time range using index" },
{ op: "Aggregate", cost: 30, rows: Math.round(tableSize * 0.001), desc: "Group by time bucket" },
{ op: "HashAggregate", cost: 25, rows: Math.round(tableSize * 0.0001), desc: "Compute AVG/MAX/MIN" },
{ op: "Sort", cost: 15, rows: Math.round(tableSize * 0.0001), desc: "Order results" },
{ op: "Return", cost: 5, rows: Math.round(tableSize * 0.0001), desc: "Return result set" }
] : [
{ op: "Sequential Scan", cost: 50, rows: tableSize, desc: "Full table scan" },
{ op: "Filter", cost: 15, rows: Math.round(tableSize * 0.1), desc: "Apply time filter" },
{ op: "Sort", cost: 20, rows: Math.round(tableSize * 0.1), desc: "Sort for grouping" },
{ op: "Aggregate", cost: 10, rows: Math.round(tableSize * 0.001), desc: "Compute aggregates" },
{ op: "Return", cost: 5, rows: Math.round(tableSize * 0.001), desc: "Return result set" }
],
latest_value: hasIndex ? [
{ op: "Index Scan Backward", cost: 15, rows: 100, desc: "Scan index in descending order" },
{ op: "Unique", cost: 10, rows: 100, desc: "Get first per device" },
{ op: "Return", cost: 5, rows: 100, desc: "Return latest values" }
] : [
{ op: "Sequential Scan", cost: 60, rows: tableSize, desc: "Full table scan" },
{ op: "Sort", cost: 25, rows: tableSize, desc: "Sort by device, time DESC" },
{ op: "Unique", cost: 10, rows: 100, desc: "Get first per device" },
{ op: "Return", cost: 5, rows: 100, desc: "Return latest values" }
],
anomaly_detection: [
{ op: hasIndex ? "Index Scan" : "Seq Scan", cost: hasIndex ? 25 : 50, rows: Math.round(tableSize * 0.1), desc: "Scan recent data" },
{ op: db === "postgresql" || db === "timescaledb" ? "Hash Join" : "Nested Loop", cost: 25, rows: Math.round(tableSize * 0.1), desc: "Join with thresholds" },
{ op: "Filter", cost: 20, rows: Math.round(tableSize * 0.01), desc: "Apply anomaly conditions" },
{ op: "Return", cost: 5, rows: Math.round(tableSize * 0.01), desc: "Return anomalies" }
],
join_operation: [
{ op: hasIndex ? "Index Scan" : "Seq Scan", cost: 30, rows: Math.round(tableSize * 0.05), desc: "Scan sensors table" },
{ op: db === "mongodb" ? "$lookup" : "Hash Join", cost: db === "mongodb" || db === "cassandra" ? 40 : 20, rows: Math.round(tableSize * 0.05), desc: "Join with devices" },
{ op: "Project", cost: 10, rows: Math.round(tableSize * 0.05), desc: "Select output columns" },
{ op: "Return", cost: 5, rows: Math.round(tableSize * 0.05), desc: "Return joined data" }
],
rollup: [
{ op: db === "timescaledb" ? "Chunk Append" : hasIndex ? "Index Scan" : "Seq Scan", cost: 35, rows: Math.round(tableSize * 0.3), desc: "Scan source data" },
{ op: "Time Bucket", cost: 15, rows: Math.round(tableSize * 0.3), desc: "Group by time interval" },
{ op: "Aggregate", cost: 30, rows: Math.round(tableSize * 0.001), desc: "Compute rollup stats" },
{ op: db === "influxdb" ? "Write to RP" : "Materialize", cost: 15, rows: Math.round(tableSize * 0.001), desc: "Store rollup data" },
{ op: "Return", cost: 5, rows: Math.round(tableSize * 0.001), desc: "Confirm completion" }
]
};
return plans[queryType] || plans.time_range;
}
// Generate optimization suggestions
function getOptimizations(db, queryType, hasIndex, perf) {
const suggestions = [];
if (!hasIndex && perf.estimatedTimeMs > 50) {
suggestions.push({
priority: "Critical",
category: "Index",
title: "Add Time-Series Index",
description: `Create index on (device_id, timestamp DESC) to improve query by ~${Math.round((1 - 0.25) * 100)}%`,
impact: "High",
effort: "Low"
});
}
if (queryType === "time_range" && perf.rowsScanned > 100000) {
if (db === "timescaledb") {
suggestions.push({
priority: "High",
category: "Aggregation",
title: "Enable Continuous Aggregates",
description: "Pre-compute time-bucket aggregations for frequently queried intervals",
impact: "High",
effort: "Medium"
});
} else if (db === "influxdb") {
suggestions.push({
priority: "High",
category: "Aggregation",
title: "Use Continuous Queries",
description: "Set up CQ to pre-aggregate data into downsampled measurements",
impact: "High",
effort: "Low"
});
} else {
suggestions.push({
priority: "High",
category: "Aggregation",
title: "Create Materialized View",
description: "Pre-compute common aggregations in a materialized view with refresh policy",
impact: "High",
effort: "Medium"
});
}
}
if (queryType === "join_operation") {
if (db === "mongodb" || db === "cassandra") {
suggestions.push({
priority: "Critical",
category: "Schema",
title: "Denormalize Data Model",
description: "Embed device metadata directly in sensor documents to avoid expensive lookups",
impact: "High",
effort: "High"
});
} else if (db === "postgresql" || db === "timescaledb") {
suggestions.push({
priority: "Medium",
category: "Index",
title: "Add Foreign Key Index",
description: "Create index on device_id in sensors table for faster hash joins",
impact: "Medium",
effort: "Low"
});
}
}
if (perf.ioCost > 40) {
suggestions.push({
priority: "Medium",
category: "Memory",
title: "Increase Buffer Pool Size",
description: "Allocate more memory for data caching to reduce disk I/O",
impact: "Medium",
effort: "Low"
});
}
if (db === "postgresql" && perf.rowsScanned > 500000) {
suggestions.push({
priority: "Medium",
category: "Partitioning",
title: "Enable Table Partitioning",
description: "Partition by time range to improve query pruning and maintenance",
impact: "High",
effort: "High"
});
}
if (perf.concurrentQueries > 50 && perf.throughputQPS < 1000) {
suggestions.push({
priority: "High",
category: "Scaling",
title: "Add Read Replicas",
description: "Distribute read load across multiple replicas for higher throughput",
impact: "High",
effort: "High"
});
}
return suggestions.slice(0, 4);
}
// State management
let state = {
db1: "influxdb",
db2: "postgresql",
queryType: "time_range",
tableSize: 10000000,
hasIndex: true,
concurrentQueries: 10,
compareMode: false,
customQuery: ""
};
// Create main container
const container = d3.create("div")
.style("width", "100%")
.style("max-width", "1200px")
.style("margin", "0 auto")
.style("font-family", "system-ui, -apple-system, sans-serif");
// =====================
// HEADER SECTION
// =====================
const header = container.append("div")
.style("background", `linear-gradient(135deg, ${colors.navy} 0%, ${colors.blue} 100%)`)
.style("color", colors.white)
.style("padding", "25px 30px")
.style("border-radius", "12px 12px 0 0")
.style("text-align", "center");
header.append("h2")
.style("margin", "0 0 10px 0")
.style("font-size", "26px")
.text("Query Performance Analyzer");
header.append("p")
.style("margin", "0")
.style("opacity", "0.9")
.style("font-size", "14px")
.text("Analyze and optimize database queries for IoT workloads");
// =====================
// MAIN LAYOUT
// =====================
const mainLayout = container.append("div")
.style("display", "grid")
.style("grid-template-columns", "320px 1fr")
.style("gap", "0")
.style("background", colors.lightGray);
// =====================
// LEFT SIDEBAR - CONTROLS
// =====================
const sidebar = mainLayout.append("div")
.style("background", colors.white)
.style("padding", "20px")
.style("border-right", `1px solid ${colors.lightGray}`);
// Database selector
sidebar.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 12px 0")
.style("font-size", "14px")
.text("Primary Database");
const db1Select = sidebar.append("select")
.style("width", "100%")
.style("padding", "10px")
.style("border", `2px solid ${colors.teal}`)
.style("border-radius", "6px")
.style("font-size", "13px")
.style("margin-bottom", "15px")
.on("change", function() {
state.db1 = this.value;
updateQueryExample();
updateAll();
});
Object.entries(databases).forEach(([key, db]) => {
db1Select.append("option")
.attr("value", key)
.attr("selected", key === state.db1 ? true : null)
.text(`${db.name} (${db.type})`);
});
// Comparison mode toggle
const compareToggle = sidebar.append("div")
.style("display", "flex")
.style("align-items", "center")
.style("margin-bottom", "15px")
.style("padding", "12px")
.style("background", colors.lightGray)
.style("border-radius", "6px")
.style("cursor", "pointer")
.on("click", function() {
state.compareMode = !state.compareMode;
compareToggle.select("input").property("checked", state.compareMode);
db2Section.style("display", state.compareMode ? "block" : "none");
updateAll();
});
compareToggle.append("input")
.attr("type", "checkbox")
.style("margin-right", "10px")
.style("width", "18px")
.style("height", "18px")
.style("cursor", "pointer");
compareToggle.append("span")
.style("font-size", "13px")
.style("font-weight", "600")
.style("color", colors.navy)
.text("Enable Comparison Mode");
// Secondary database selector (hidden by default)
const db2Section = sidebar.append("div")
.style("display", "none")
.style("margin-bottom", "15px");
db2Section.append("label")
.style("display", "block")
.style("font-size", "12px")
.style("color", colors.gray)
.style("margin-bottom", "6px")
.text("Compare With");
const db2Select = db2Section.append("select")
.style("width", "100%")
.style("padding", "10px")
.style("border", `2px solid ${colors.orange}`)
.style("border-radius", "6px")
.style("font-size", "13px")
.on("change", function() {
state.db2 = this.value;
updateAll();
});
Object.entries(databases).forEach(([key, db]) => {
db2Select.append("option")
.attr("value", key)
.attr("selected", key === state.db2 ? true : null)
.text(`${db.name} (${db.type})`);
});
// Query type selector
sidebar.append("h3")
.style("color", colors.navy)
.style("margin", "20px 0 12px 0")
.style("font-size", "14px")
.text("Query Pattern");
const querySelect = sidebar.append("select")
.style("width", "100%")
.style("padding", "10px")
.style("border", `2px solid ${colors.navy}`)
.style("border-radius", "6px")
.style("font-size", "13px")
.style("margin-bottom", "8px")
.on("change", function() {
state.queryType = this.value;
updateQueryExample();
updateAll();
});
Object.entries(queryTypes).forEach(([key, qt]) => {
querySelect.append("option")
.attr("value", key)
.attr("selected", key === state.queryType ? true : null)
.text(qt.name);
});
const queryDesc = sidebar.append("div")
.attr("class", "query-description")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "15px")
.style("padding", "10px")
.style("background", colors.lightGray)
.style("border-radius", "6px")
.text(queryTypes[state.queryType].description);
// Data characteristics section
sidebar.append("h3")
.style("color", colors.navy)
.style("margin", "20px 0 12px 0")
.style("font-size", "14px")
.text("Data Characteristics");
// Table size slider
const sizeGroup = sidebar.append("div")
.style("margin-bottom", "15px");
const sizeLabel = sizeGroup.append("div")
.style("display", "flex")
.style("justify-content", "space-between")
.style("margin-bottom", "6px");
sizeLabel.append("span")
.style("font-size", "12px")
.style("color", colors.navy)
.text("Table Size");
const sizeValue = sizeLabel.append("span")
.attr("class", "size-value")
.style("font-size", "12px")
.style("font-weight", "bold")
.style("color", colors.teal)
.text("10M rows");
sizeGroup.append("input")
.attr("type", "range")
.attr("min", "5")
.attr("max", "9")
.attr("value", "7")
.style("width", "100%")
.style("accent-color", colors.teal)
.on("input", function() {
state.tableSize = Math.pow(10, parseInt(this.value));
const labels = { 5: "100K", 6: "1M", 7: "10M", 8: "100M", 9: "1B" };
d3.select(".size-value").text(`${labels[this.value]} rows`);
updateAll();
});
sizeGroup.append("div")
.style("display", "flex")
.style("justify-content", "space-between")
.style("font-size", "9px")
.style("color", colors.gray)
.html("<span>100K</span><span>1M</span><span>10M</span><span>100M</span><span>1B</span>");
// Concurrent queries slider
const concGroup = sidebar.append("div")
.style("margin-bottom", "15px");
const concLabel = concGroup.append("div")
.style("display", "flex")
.style("justify-content", "space-between")
.style("margin-bottom", "6px");
concLabel.append("span")
.style("font-size", "12px")
.style("color", colors.navy)
.text("Concurrent Queries");
const concValue = concLabel.append("span")
.attr("class", "conc-value")
.style("font-size", "12px")
.style("font-weight", "bold")
.style("color", colors.orange)
.text("10");
concGroup.append("input")
.attr("type", "range")
.attr("min", "1")
.attr("max", "100")
.attr("value", "10")
.style("width", "100%")
.style("accent-color", colors.orange)
.on("input", function() {
state.concurrentQueries = parseInt(this.value);
d3.select(".conc-value").text(this.value);
updateAll();
});
concGroup.append("div")
.style("display", "flex")
.style("justify-content", "space-between")
.style("font-size", "9px")
.style("color", colors.gray)
.html("<span>1</span><span>25</span><span>50</span><span>75</span><span>100</span>");
// Index toggle
const indexGroup = sidebar.append("div")
.style("display", "flex")
.style("align-items", "center")
.style("margin-bottom", "15px")
.style("padding", "12px")
.style("background", colors.lightGray)
.style("border-radius", "6px")
.style("cursor", "pointer")
.on("click", function() {
state.hasIndex = !state.hasIndex;
indexGroup.select("input").property("checked", state.hasIndex);
updateAll();
});
indexGroup.append("input")
.attr("type", "checkbox")
.attr("checked", true)
.style("margin-right", "10px")
.style("width", "18px")
.style("height", "18px")
.style("cursor", "pointer");
indexGroup.append("span")
.style("font-size", "13px")
.style("font-weight", "600")
.style("color", colors.navy)
.text("Index Available");
// Analyze button
const analyzeBtn = sidebar.append("button")
.style("width", "100%")
.style("padding", "14px")
.style("background", `linear-gradient(135deg, ${colors.teal}, ${colors.green})`)
.style("color", colors.white)
.style("border", "none")
.style("border-radius", "8px")
.style("font-size", "14px")
.style("font-weight", "bold")
.style("cursor", "pointer")
.style("margin-top", "10px")
.text("Analyze Query")
.on("click", updateAll);
// =====================
// RIGHT PANEL - RESULTS
// =====================
const resultsPanel = mainLayout.append("div")
.style("padding", "20px")
.style("overflow-y", "auto");
// Query Editor Section
const querySection = resultsPanel.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("margin-bottom", "15px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)");
querySection.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 12px 0")
.style("font-size", "16px")
.text("Sample Query");
const queryEditor = querySection.append("pre")
.attr("class", "query-editor")
.style("background", "#1e1e1e")
.style("color", "#d4d4d4")
.style("padding", "15px")
.style("border-radius", "8px")
.style("font-size", "12px")
.style("font-family", "'Fira Code', 'Monaco', monospace")
.style("overflow-x", "auto")
.style("white-space", "pre-wrap")
.style("word-break", "break-word")
.style("max-height", "200px")
.text(queryTypes[state.queryType].examples[state.db1]);
// Performance Metrics Cards
const metricsSection = resultsPanel.append("div")
.attr("class", "metrics-section")
.style("display", "grid")
.style("grid-template-columns", "1fr")
.style("gap", "15px")
.style("margin-bottom", "15px");
// Execution Plan Section
const planSection = resultsPanel.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("margin-bottom", "15px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)");
planSection.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 15px 0")
.style("font-size", "16px")
.text("Execution Plan");
const planContainer = planSection.append("div")
.attr("class", "plan-container");
// Cost Breakdown Section
const costSection = resultsPanel.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("margin-bottom", "15px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)");
costSection.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 15px 0")
.style("font-size", "16px")
.text("Cost Breakdown");
const costContainer = costSection.append("div")
.attr("class", "cost-container");
// Throughput Calculator
const throughputSection = resultsPanel.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("margin-bottom", "15px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)");
throughputSection.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 15px 0")
.style("font-size", "16px")
.text("Throughput Calculator");
const throughputContainer = throughputSection.append("div")
.attr("class", "throughput-container");
// Recommendations Section
const recSection = resultsPanel.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)");
recSection.append("h3")
.style("color", colors.navy)
.style("margin", "0 0 15px 0")
.style("font-size", "16px")
.text("Optimization Recommendations");
const recContainer = recSection.append("div")
.attr("class", "recommendations-container");
// Index suggestion section
const indexSection = recSection.append("div")
.attr("class", "index-section")
.style("margin-top", "20px")
.style("padding-top", "15px")
.style("border-top", `1px solid ${colors.lightGray}`);
// =====================
// FOOTER
// =====================
const footer = container.append("div")
.style("background", colors.navy)
.style("color", colors.white)
.style("padding", "15px 20px")
.style("border-radius", "0 0 12px 12px")
.style("text-align", "center")
.style("font-size", "12px");
footer.append("span")
.text("Performance estimates are simulated based on typical database behavior | ");
footer.append("span")
.style("opacity", "0.8")
.text("Actual results depend on hardware, configuration, and data distribution");
// =====================
// UPDATE FUNCTIONS
// =====================
function updateQueryExample() {
const example = queryTypes[state.queryType].examples[state.db1];
d3.select(".query-editor").text(example);
d3.select(".query-description").text(queryTypes[state.queryType].description);
}
function formatNumber(num) {
if (num >= 1000000000) return (num / 1000000000).toFixed(1) + "B";
if (num >= 1000000) return (num / 1000000).toFixed(1) + "M";
if (num >= 1000) return (num / 1000).toFixed(1) + "K";
return num.toString();
}
function updateMetrics() {
metricsSection.selectAll("*").remove();
metricsSection.style("grid-template-columns", state.compareMode ? "1fr 1fr" : "1fr");
const perf1 = calculatePerformance(state.db1, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db1Info = databases[state.db1];
function renderMetricCard(perf, db) {
const card = metricsSection.append("div")
.style("background", colors.white)
.style("border-radius", "10px")
.style("padding", "20px")
.style("box-shadow", "0 2px 8px rgba(0,0,0,0.08)")
.style("border-top", `4px solid ${db.color}`);
card.append("h4")
.style("color", db.color)
.style("margin", "0 0 15px 0")
.style("font-size", "15px")
.text(db.name);
const metricsGrid = card.append("div")
.style("display", "grid")
.style("grid-template-columns", "repeat(2, 1fr)")
.style("gap", "12px");
const metrics = [
{
label: "Estimated Latency",
value: `${perf.estimatedTimeMs} ms`,
color: perf.estimatedTimeMs > 500 ? colors.red : perf.estimatedTimeMs > 100 ? colors.orange : colors.green,
icon: "⏱️"
},
{
label: "Rows Scanned",
value: formatNumber(perf.rowsScanned),
color: colors.navy,
icon: "📊"
},
{
label: "Memory Usage",
value: `${perf.memoryMB} MB`,
color: perf.memoryMB > 100 ? colors.orange : colors.teal,
icon: "💾"
},
{
label: "Index Used",
value: perf.indexUsed ? "Yes" : "No",
color: perf.indexUsed ? colors.green : colors.red,
icon: "🔍"
},
{
label: "Throughput",
value: `${formatNumber(perf.throughputQPS)} QPS`,
color: perf.throughputQPS > 1000 ? colors.green : colors.orange,
icon: "⚡"
},
{
label: "Concurrency",
value: `${perf.concurrentQueries} queries`,
color: colors.navy,
icon: "🔄"
}
];
metrics.forEach(m => {
const metricBox = metricsGrid.append("div")
.style("background", colors.lightGray)
.style("padding", "12px")
.style("border-radius", "8px");
metricBox.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "4px")
.text(`${m.icon} ${m.label}`);
metricBox.append("div")
.style("font-size", "16px")
.style("font-weight", "bold")
.style("color", m.color)
.text(m.value);
});
}
renderMetricCard(perf1, db1Info);
if (state.compareMode) {
const perf2 = calculatePerformance(state.db2, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db2Info = databases[state.db2];
renderMetricCard(perf2, db2Info);
}
}
function updateExecutionPlan() {
planContainer.selectAll("*").remove();
const plan1 = generateExecutionPlan(state.db1, state.queryType, state.hasIndex, state.tableSize);
const db1Info = databases[state.db1];
function renderPlan(plan, db, containerId) {
const planDiv = planContainer.append("div")
.style("margin-bottom", state.compareMode ? "20px" : "0");
if (state.compareMode) {
planDiv.append("div")
.style("font-weight", "bold")
.style("color", db.color)
.style("margin-bottom", "10px")
.style("font-size", "13px")
.text(db.name);
}
const svgWidth = 700;
const svgHeight = 120;
const nodeWidth = 110;
const nodeHeight = 50;
const stepX = 140;
const svg = planDiv.append("svg")
.attr("viewBox", `0 0 ${svgWidth} ${svgHeight}`)
.attr("width", "100%")
.style("max-height", "120px");
// Arrow marker
svg.append("defs").append("marker")
.attr("id", `arrow-${containerId}`)
.attr("viewBox", "0 0 10 10")
.attr("refX", 9)
.attr("refY", 5)
.attr("markerWidth", 6)
.attr("markerHeight", 6)
.attr("orient", "auto")
.append("path")
.attr("d", "M 0 0 L 10 5 L 0 10 z")
.attr("fill", colors.gray);
plan.forEach((step, idx) => {
const x = 20 + idx * stepX;
const y = 35;
// Connection line
if (idx > 0) {
svg.append("line")
.attr("x1", x - stepX + nodeWidth + 5)
.attr("x2", x - 5)
.attr("y1", y + nodeHeight / 2)
.attr("y2", y + nodeHeight / 2)
.attr("stroke", colors.gray)
.attr("stroke-width", 2)
.attr("marker-end", `url(#arrow-${containerId})`);
}
// Node
const g = svg.append("g")
.attr("transform", `translate(${x}, ${y})`);
const costColor = step.cost > 40 ? colors.red : step.cost > 20 ? colors.orange : colors.green;
g.append("rect")
.attr("width", nodeWidth)
.attr("height", nodeHeight)
.attr("rx", 6)
.attr("fill", costColor)
.attr("fill-opacity", 0.15)
.attr("stroke", costColor)
.attr("stroke-width", 2);
g.append("text")
.attr("x", nodeWidth / 2)
.attr("y", 18)
.attr("text-anchor", "middle")
.attr("font-size", "10px")
.attr("font-weight", "bold")
.attr("fill", colors.navy)
.text(step.op.substring(0, 15));
g.append("text")
.attr("x", nodeWidth / 2)
.attr("y", 32)
.attr("text-anchor", "middle")
.attr("font-size", "8px")
.attr("fill", colors.gray)
.text(`Cost: ${step.cost}%`);
g.append("text")
.attr("x", nodeWidth / 2)
.attr("y", 44)
.attr("text-anchor", "middle")
.attr("font-size", "8px")
.attr("fill", colors.gray)
.text(`Rows: ${formatNumber(step.rows)}`);
});
}
renderPlan(plan1, db1Info, "plan1");
if (state.compareMode) {
const plan2 = generateExecutionPlan(state.db2, state.queryType, state.hasIndex, state.tableSize);
const db2Info = databases[state.db2];
renderPlan(plan2, db2Info, "plan2");
}
}
function updateCostBreakdown() {
costContainer.selectAll("*").remove();
const perf1 = calculatePerformance(state.db1, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db1Info = databases[state.db1];
const svgWidth = 600;
const svgHeight = state.compareMode ? 140 : 80;
const barHeight = 30;
const barY1 = 25;
const barY2 = 75;
const maxWidth = 450;
const leftMargin = 100;
const svg = costContainer.append("svg")
.attr("viewBox", `0 0 ${svgWidth} ${svgHeight}`)
.attr("width", "100%");
function renderCostBar(perf, db, y) {
const cpuWidth = (perf.cpuCost / 100) * maxWidth;
const ioWidth = (perf.ioCost / 100) * maxWidth;
const memWidth = (perf.memoryCost / 100) * maxWidth;
const netWidth = (perf.networkCost / 100) * maxWidth;
// Label
svg.append("text")
.attr("x", leftMargin - 10)
.attr("y", y + barHeight / 2 + 4)
.attr("text-anchor", "end")
.attr("font-size", "12px")
.attr("font-weight", "bold")
.attr("fill", db.color)
.text(db.name);
// CPU bar
let currentX = leftMargin;
svg.append("rect")
.attr("x", currentX)
.attr("y", y)
.attr("width", cpuWidth)
.attr("height", barHeight)
.attr("fill", colors.blue)
.attr("rx", 3);
if (cpuWidth > 30) {
svg.append("text")
.attr("x", currentX + cpuWidth / 2)
.attr("y", y + barHeight / 2 + 4)
.attr("text-anchor", "middle")
.attr("font-size", "10px")
.attr("fill", colors.white)
.attr("font-weight", "bold")
.text(`CPU ${perf.cpuCost}%`);
}
// I/O bar
currentX += cpuWidth;
svg.append("rect")
.attr("x", currentX)
.attr("y", y)
.attr("width", ioWidth)
.attr("height", barHeight)
.attr("fill", colors.orange);
if (ioWidth > 30) {
svg.append("text")
.attr("x", currentX + ioWidth / 2)
.attr("y", y + barHeight / 2 + 4)
.attr("text-anchor", "middle")
.attr("font-size", "10px")
.attr("fill", colors.white)
.attr("font-weight", "bold")
.text(`I/O ${perf.ioCost}%`);
}
// Memory bar
currentX += ioWidth;
svg.append("rect")
.attr("x", currentX)
.attr("y", y)
.attr("width", memWidth)
.attr("height", barHeight)
.attr("fill", colors.purple);
if (memWidth > 30) {
svg.append("text")
.attr("x", currentX + memWidth / 2)
.attr("y", y + barHeight / 2 + 4)
.attr("text-anchor", "middle")
.attr("font-size", "10px")
.attr("fill", colors.white)
.attr("font-weight", "bold")
.text(`Mem ${perf.memoryCost}%`);
}
// Network bar
currentX += memWidth;
svg.append("rect")
.attr("x", currentX)
.attr("y", y)
.attr("width", netWidth)
.attr("height", barHeight)
.attr("fill", colors.teal)
.attr("rx", 3);
if (netWidth > 30) {
svg.append("text")
.attr("x", currentX + netWidth / 2)
.attr("y", y + barHeight / 2 + 4)
.attr("text-anchor", "middle")
.attr("font-size", "10px")
.attr("fill", colors.white)
.attr("font-weight", "bold")
.text(`Net ${perf.networkCost}%`);
}
}
renderCostBar(perf1, db1Info, barY1);
if (state.compareMode) {
const perf2 = calculatePerformance(state.db2, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db2Info = databases[state.db2];
renderCostBar(perf2, db2Info, barY2);
}
// Legend
const legendY = state.compareMode ? 120 : 65;
const legendItems = [
{ color: colors.blue, label: "CPU" },
{ color: colors.orange, label: "I/O" },
{ color: colors.purple, label: "Memory" },
{ color: colors.teal, label: "Network" }
];
legendItems.forEach((item, idx) => {
svg.append("rect")
.attr("x", leftMargin + idx * 90)
.attr("y", legendY)
.attr("width", 12)
.attr("height", 12)
.attr("fill", item.color)
.attr("rx", 2);
svg.append("text")
.attr("x", leftMargin + 16 + idx * 90)
.attr("y", legendY + 10)
.attr("font-size", "10px")
.attr("fill", colors.navy)
.text(item.label);
});
}
function updateThroughput() {
throughputContainer.selectAll("*").remove();
const perf1 = calculatePerformance(state.db1, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db1Info = databases[state.db1];
const grid = throughputContainer.append("div")
.style("display", "grid")
.style("grid-template-columns", state.compareMode ? "1fr 1fr" : "1fr 1fr 1fr")
.style("gap", "15px");
function renderThroughputCard(perf, db) {
const card = grid.append("div")
.style("background", `linear-gradient(135deg, ${db.color}20, ${db.color}10)`)
.style("border", `2px solid ${db.color}`)
.style("border-radius", "10px")
.style("padding", "20px")
.style("text-align", "center");
card.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "5px")
.text(db.name);
card.append("div")
.style("font-size", "28px")
.style("font-weight", "bold")
.style("color", db.color)
.text(formatNumber(perf.throughputQPS));
card.append("div")
.style("font-size", "12px")
.style("color", colors.navy)
.text("queries/sec");
card.append("div")
.style("font-size", "10px")
.style("color", colors.gray)
.style("margin-top", "8px")
.text(`@ ${state.concurrentQueries} concurrent`);
}
renderThroughputCard(perf1, db1Info);
if (state.compareMode) {
const perf2 = calculatePerformance(state.db2, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const db2Info = databases[state.db2];
renderThroughputCard(perf2, db2Info);
// Comparison card
const compCard = grid.append("div")
.style("background", colors.lightGray)
.style("border-radius", "10px")
.style("padding", "20px")
.style("text-align", "center");
const ratio = perf1.throughputQPS / perf2.throughputQPS;
const winner = ratio > 1 ? db1Info : db2Info;
const diff = Math.abs(ratio - 1) * 100;
compCard.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "5px")
.text("Comparison");
compCard.append("div")
.style("font-size", "20px")
.style("font-weight", "bold")
.style("color", winner.color)
.text(`${winner.name} wins`);
compCard.append("div")
.style("font-size", "14px")
.style("color", colors.navy)
.style("margin-top", "5px")
.text(`${diff.toFixed(0)}% faster`);
} else {
// Additional stats for single DB mode
const statsCard = grid.append("div")
.style("background", colors.lightGray)
.style("border-radius", "10px")
.style("padding", "20px")
.style("text-align", "center");
statsCard.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "5px")
.text("Daily Capacity");
statsCard.append("div")
.style("font-size", "24px")
.style("font-weight", "bold")
.style("color", colors.navy)
.text(formatNumber(perf1.throughputQPS * 86400));
statsCard.append("div")
.style("font-size", "12px")
.style("color", colors.gray)
.text("queries/day");
const hourlyCard = grid.append("div")
.style("background", colors.lightGray)
.style("border-radius", "10px")
.style("padding", "20px")
.style("text-align", "center");
hourlyCard.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.style("margin-bottom", "5px")
.text("Hourly Capacity");
hourlyCard.append("div")
.style("font-size", "24px")
.style("font-weight", "bold")
.style("color", colors.navy)
.text(formatNumber(perf1.throughputQPS * 3600));
hourlyCard.append("div")
.style("font-size", "12px")
.style("color", colors.gray)
.text("queries/hour");
}
}
function updateRecommendations() {
recContainer.selectAll("*").remove();
const perf1 = calculatePerformance(state.db1, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const opts1 = getOptimizations(state.db1, state.queryType, state.hasIndex, perf1);
const db1Info = databases[state.db1];
function renderRecommendations(opts, db) {
if (opts.length === 0) {
recContainer.append("div")
.style("padding", "15px")
.style("background", `${colors.green}15`)
.style("border-radius", "8px")
.style("color", colors.green)
.style("font-weight", "600")
.style("text-align", "center")
.text(`${db.name}: Query is well-optimized for your configuration!`);
return;
}
const section = recContainer.append("div")
.style("margin-bottom", state.compareMode ? "20px" : "0");
if (state.compareMode) {
section.append("div")
.style("font-weight", "bold")
.style("color", db.color)
.style("font-size", "13px")
.style("margin-bottom", "10px")
.text(db.name);
}
opts.forEach((opt, idx) => {
const priorityColors = {
"Critical": colors.red,
"High": colors.orange,
"Medium": colors.teal,
"Low": colors.gray
};
const item = section.append("div")
.style("display", "flex")
.style("align-items", "flex-start")
.style("padding", "12px")
.style("background", colors.lightGray)
.style("border-radius", "8px")
.style("margin-bottom", "10px")
.style("border-left", `4px solid ${priorityColors[opt.priority]}`);
const badge = item.append("div")
.style("display", "flex")
.style("flex-direction", "column")
.style("align-items", "center")
.style("margin-right", "15px")
.style("min-width", "60px");
badge.append("span")
.style("background", priorityColors[opt.priority])
.style("color", colors.white)
.style("padding", "3px 8px")
.style("border-radius", "10px")
.style("font-size", "9px")
.style("font-weight", "bold")
.text(opt.priority);
badge.append("span")
.style("font-size", "9px")
.style("color", colors.gray)
.style("margin-top", "4px")
.text(opt.category);
const textDiv = item.append("div")
.style("flex", "1");
textDiv.append("div")
.style("font-weight", "bold")
.style("font-size", "13px")
.style("color", colors.navy)
.style("margin-bottom", "4px")
.text(opt.title);
textDiv.append("div")
.style("font-size", "11px")
.style("color", colors.gray)
.text(opt.description);
const impactDiv = item.append("div")
.style("text-align", "right")
.style("min-width", "70px");
impactDiv.append("div")
.style("font-size", "9px")
.style("color", colors.gray)
.text("Impact");
impactDiv.append("div")
.style("font-size", "11px")
.style("font-weight", "bold")
.style("color", opt.impact === "High" ? colors.green : colors.teal)
.text(opt.impact);
});
}
renderRecommendations(opts1, db1Info);
if (state.compareMode) {
const perf2 = calculatePerformance(state.db2, state.queryType, state.tableSize, state.hasIndex, state.concurrentQueries);
const opts2 = getOptimizations(state.db2, state.queryType, state.hasIndex, perf2);
const db2Info = databases[state.db2];
renderRecommendations(opts2, db2Info);
}
}
function updateIndexSuggestions() {
indexSection.selectAll("*").remove();
indexSection.append("h4")
.style("color", colors.navy)
.style("margin", "0 0 12px 0")
.style("font-size", "14px")
.text("Suggested Index / Optimization");
const indexSuggestions = {
time_range: {
influxdb: `-- InfluxDB uses time-based sharding automatically
-- Optimize with appropriate retention policies
CREATE RETENTION POLICY "hot_data" ON "iot_db"
DURATION 7d REPLICATION 1 DEFAULT`,
timescaledb: `-- Create hypertable with appropriate chunk interval
SELECT create_hypertable('sensors', 'timestamp',
chunk_time_interval => INTERVAL '1 day');
-- Add composite index for common queries
CREATE INDEX idx_sensors_device_time
ON sensors (device_id, timestamp DESC);`,
mongodb: `// Create compound index for time-range queries
db.sensors.createIndex(
{ "device_id": 1, "timestamp": -1 },
{ name: "device_time_idx" }
);
// Enable time-series collection (MongoDB 5.0+)
db.createCollection("sensors", {
timeseries: { timeField: "timestamp", metaField: "device_id" }
});`,
postgresql: `-- Use BRIN index for time-ordered data (space efficient)
CREATE INDEX idx_sensors_brin
ON sensors USING BRIN (timestamp);
-- Or B-tree for faster point queries
CREATE INDEX idx_sensors_device_time
ON sensors (device_id, timestamp DESC);`,
cassandra: `-- Design table with time-based partitioning
CREATE TABLE sensors (
device_id text,
date_bucket date,
timestamp timestamp,
temperature double,
PRIMARY KEY ((device_id, date_bucket), timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);`
},
latest_value: {
influxdb: `-- Use last() function with GROUP BY
-- Consider keeping separate "latest" measurement
SELECT last(*) FROM sensors GROUP BY device_id`,
timescaledb: `-- Create index for DISTINCT ON queries
CREATE INDEX idx_sensors_device_time_desc
ON sensors (device_id, timestamp DESC);
-- Or use continuous aggregate for latest values
CREATE MATERIALIZED VIEW latest_readings
WITH (timescaledb.continuous) AS
SELECT device_id, last(temperature, timestamp), last(humidity, timestamp)
FROM sensors
GROUP BY device_id;`,
mongodb: `// Create index for latest value lookup
db.sensors.createIndex({ "device_id": 1, "timestamp": -1 });
// Consider maintaining a separate "latest" collection
// updated on each insert`,
postgresql: `-- Index for DISTINCT ON pattern
CREATE INDEX idx_sensors_device_time_desc
ON sensors (device_id, timestamp DESC);
-- Consider trigger-maintained latest values table`,
cassandra: `-- Maintain separate table for latest values
CREATE TABLE sensors_latest (
device_id text PRIMARY KEY,
timestamp timestamp,
temperature double,
humidity double
);`
},
anomaly_detection: {
influxdb: `-- Pre-compute thresholds in Kapacitor or tasks
-- Use subqueries for dynamic threshold comparison`,
timescaledb: `-- Index for threshold joins
CREATE INDEX idx_sensors_device ON sensors (device_id);
-- Precompute statistics with continuous aggregate
CREATE MATERIALIZED VIEW device_stats
WITH (timescaledb.continuous) AS
SELECT device_id,
time_bucket('1 day', timestamp) AS day,
avg(temperature), stddev(temperature)
FROM sensors
GROUP BY device_id, day;`,
mongodb: `// Index for threshold lookup
db.sensors.createIndex({ "device_id": 1, "timestamp": -1 });
// Consider embedding thresholds in sensor documents`,
postgresql: `-- Index for join with thresholds
CREATE INDEX idx_sensors_device ON sensors (device_id);
CREATE INDEX idx_thresholds_device ON device_thresholds (device_id);
-- Partial index for recent data
CREATE INDEX idx_sensors_recent ON sensors (device_id, timestamp)
WHERE timestamp > NOW() - INTERVAL '1 day';`,
cassandra: `-- Denormalize thresholds into sensor table
-- Cannot efficiently filter by computed values
-- Consider pre-processing with Spark`
},
join_operation: {
influxdb: `-- InfluxDB: Avoid joins - embed metadata as tags
-- Use tag cardinality wisely`,
timescaledb: `-- Foreign key index for efficient joins
CREATE INDEX idx_sensors_device_fk ON sensors (device_id);
-- Consider hypertable foreign key constraints
ALTER TABLE sensors ADD CONSTRAINT fk_device
FOREIGN KEY (device_id) REFERENCES devices(id);`,
mongodb: `// $lookup optimization
db.sensors.createIndex({ "device_id": 1 });
// Better: embed device info in sensor documents
// { device_id, location, type, temperature, ... }`,
postgresql: `-- Index foreign key for hash join
CREATE INDEX idx_sensors_device_id ON sensors (device_id);
-- Analyze tables for query planner
ANALYZE sensors;
ANALYZE devices;`,
cassandra: `-- Denormalize: embed device metadata
CREATE TABLE sensors_with_device (
device_id text,
timestamp timestamp,
temperature double,
location text, -- denormalized
device_type text, -- denormalized
PRIMARY KEY (device_id, timestamp)
);`
},
rollup: {
influxdb: `-- Continuous Query for automatic rollup
CREATE CONTINUOUS QUERY "hourly_rollup" ON "iot_db"
BEGIN
SELECT mean(temperature), max(temperature), min(temperature)
INTO "sensors_hourly"
FROM "sensors"
GROUP BY time(1h), device_id
END`,
timescaledb: `-- Continuous aggregate with automatic refresh
CREATE MATERIALIZED VIEW sensors_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', timestamp) AS bucket,
device_id,
avg(temperature), min(temperature), max(temperature),
count(*)
FROM sensors
GROUP BY bucket, device_id;
SELECT add_continuous_aggregate_policy('sensors_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');`,
mongodb: `// Use aggregation pipeline with $out
// Schedule with cron or change streams
db.sensors.aggregate([
{ $match: { timestamp: { $gte: hourAgo } } },
{ $group: {
_id: { device: "$device_id", hour: { $dateTrunc: { date: "$timestamp", unit: "hour" } } },
avgTemp: { $avg: "$temperature" },
count: { $sum: 1 }
}},
{ $merge: { into: "sensors_hourly", whenMatched: "replace" } }
]);`,
postgresql: `-- Materialized view with refresh
CREATE MATERIALIZED VIEW sensors_hourly AS
SELECT date_trunc('hour', timestamp) AS hour,
device_id,
avg(temperature), min(temperature), max(temperature),
count(*)
FROM sensors
GROUP BY hour, device_id;
-- Unique index for concurrent refresh
CREATE UNIQUE INDEX ON sensors_hourly (hour, device_id);
-- Schedule refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY sensors_hourly;`,
cassandra: `-- Pre-aggregate during write using application logic
-- Or use Spark for batch rollups
INSERT INTO sensors_hourly
(device_id, hour, avg_temp, min_temp, max_temp, count)
VALUES (?, ?, ?, ?, ?, ?)
USING TTL 7776000; -- 90 days retention`
}
};
const suggestion = indexSuggestions[state.queryType]?.[state.db1] || "No specific recommendation";
indexSection.append("pre")
.style("background", "#1e1e1e")
.style("color", "#d4d4d4")
.style("padding", "15px")
.style("border-radius", "8px")
.style("font-size", "11px")
.style("font-family", "'Fira Code', 'Monaco', monospace")
.style("overflow-x", "auto")
.style("white-space", "pre-wrap")
.style("word-break", "break-word")
.style("max-height", "250px")
.text(suggestion);
if (!state.hasIndex) {
indexSection.append("div")
.style("margin-top", "12px")
.style("padding", "12px")
.style("background", `${colors.orange}15`)
.style("border-radius", "8px")
.style("border-left", `4px solid ${colors.orange}`)
.style("font-size", "12px")
.style("color", colors.navy)
.html(`<strong>Note:</strong> Enable "Index Available" toggle to see significant performance improvements with proper indexing.`);
}
}
function updateAll() {
updateMetrics();
updateExecutionPlan();
updateCostBreakdown();
updateThroughput();
updateRecommendations();
updateIndexSuggestions();
}
// Initial render
updateQueryExample();
updateAll();
return container.node();
}