Week 05 ‐ Devlog

Indexes and Query Planning

Large dataset, enough to find something meaningful to use EXPLAIN ANALYZE for, especially before indexing. Let's seed 100K data_logs and see what the baseline in performance is.

Check for how many rows are currently in data_logs before starting:

SELECT COUNT(*) FROM public.data_logs;

There should only be 2 rows in the data_logs.

Run the seed and generate 100,000 rows with randomized values across the two existing plants.

INSERT INTO public.data_logs (plant_id, user_id, metric_type, metric_label, value, unit, logged_at)
SELECT
  CASE WHEN random() < 0.5
    THEN 'bbbbbbbb-0000-0000-0000-000000000001'::uuid
    ELSE 'bbbbbbbb-0000-0000-0000-000000000002'::uuid
  END AS plant_id,
  'b8b9ec50-6b0a-4c59-aaaa-8effecf78903'::uuid AS user_id,
  (ARRAY['ph','ec','temperature','humidity'])[floor(random() * 4 + 1)::int]::metric_type AS metric_type,
  NULL AS metric_label,
  round((random() * 100)::numeric, 2) AS value,
  (ARRAY['pH','mS/cm','F','%'])[floor(random() * 4 + 1)::int] AS unit,
  now() - (random() * interval '365 days') AS logged_at
FROM generate_series(1, 100000);

Verification on the row count has been seeded:

SELECT COUNT(*) FROM public.data_logs;

Returns 100,000 rows in both SQL and Table View Editor. One more query to confirm the distribution looks randomized:

SELECT metric_type, COUNT(*)
FROM public.data_logs
GROUP BY metric_type
ORDER BY metric_type;

This helps a lot with performance testing and sometimes can take a little while to seed. This shows ph, ec, temperature, and humidity with its different metric_type counts. Finished with Issue #10 and halfway through with the Performance and JSONB Milestone, the last issue #11 will basically use the EXPLAIN ANALYZE on the seeded data and confirm index scans and record the execution time.

EXPLAIN ANALYZE
SELECT * FROM public.data_logs
WHERE metric_type = 'ph';

Result: Seq Scan(cost 2604.0, estimated 24,604 rows) 34.07ms execution time 100,002 rows scanned 74,904 were filtered out (marked by -74,904) 25,098 remained

Adding an index would reduce the rows being scanned and increase performance times:

CREATE INDEX idx_spaces_user_id      ON public.spaces(user_id);
CREATE INDEX idx_plants_space_id     ON public.plants(space_id);
CREATE INDEX idx_plants_user_id      ON public.plants(user_id);
CREATE INDEX idx_data_logs_plant_id  ON public.data_logs(plant_id);
CREATE INDEX idx_data_logs_logged_at ON public.data_logs(logged_at DESC);
CREATE INDEX idx_tasks_space_id      ON public.tasks(space_id);
CREATE INDEX idx_tasks_user_due      ON public.tasks(user_id, due_date);
CREATE INDEX idx_tasks_pending       ON public.tasks(user_id, due_date)
  WHERE status = 'pending';

And re-run the same EXPLAIN ANALYZE to see the new results:

EXPLAIN ANALYZE
SELECT * FROM public.data_logs
WHERE metric_type = 'ph';

Document the difference:

SeqScan 10.31ms/ execution 100,002 scanned -74,904 left out 25,098 rows remaining

Seemspostgresql still used a Seq Scan, so we need to add an index specifically on metric_type:

CREATE INDEX idx_data_logs_metric_type ON public.data_logs(metric_type);

Which now returns:

Index Scan using idx_data_logs_metric_type(cost 1867.4, estimated 24,604 rows) 8.64ms / 25,098 rows processed and returned

Before:

Scan type: Seq Scan
Rows scanned: 100,002
Execution time: 34.07ms

After:

Scan type: Index Scan using idx_data_logs_metric_type
Rows scanned: 25,098
Execution time: 8.64ms

Improvement:

Reduction: (34.07 - 8.64) / 34.07 * 100 = 74.6% faster
Speedup: 34.07 / 8.64 = 3.9x

Issues Worked On:

Branches:

Pull Requests:

Notes:

metric_type only has four distinct values — lower cardinality means the index is less selective.
not as dramatic as a 1m row change, but 74.6% faster