PLANET

Per-query energy accounting inside PostgreSQL 18.

EXPLAIN ANALYZE tells you how long a query took, how many rows it touched, how many blocks it read and how much WAL it wrote. It does not tell you how much electricity it used. Your power meter knows that number, but only for the whole machine, and it cannot divide it among the queries that were running.

PLANET closes that gap. It is a PostgreSQL extension that reports the dynamic energy in joules of every completed statement, from counters the executor already maintains. No hardware energy counter is read at query time, so it works unchanged in a cloud VM where RAPL is unavailable.

bench=# LOAD 'planet';
bench=# EXPLAIN (ANALYZE, PLANET, COSTS off)
bench-# SELECT count(*) FROM orders WHERE customer_id = 42;

 Aggregate (actual time=0.086..0.086 rows=1.00 loops=1)
   ->  Bitmap Heap Scan on orders (actual time=0.021..0.082 rows=40.00 loops=1)
         Recheck Cond: (customer_id = 42)
         ->  Bitmap Index Scan on orders_customer_idx (actual rows=40.00 loops=1)
               Index Cond: (customer_id = 42)
 Planning Time: 0.070 ms
 PLANET energy=0.00312 J (compute=0.00312 awake=0 io=0 wal=0)
   cpu=0.000208s wall=0.000208s blks_read=0 blks_written=0 wal=0B
 Execution Time: 0.105 ms

PLANET is the implementation behind "Query-Energy Measurement and Plan Comparison for PostgreSQL". On a wall-metered Xeon server its estimates are within 7.5% mean absolute percentage error of an AC meter inside the calibrated domain, and it adds about 4 µs per statement.

Status: research prototype. What it is good at is comparison: two plans, two settings, two schema choices, on one machine, against one calibration. The absolute joules are a calibrated estimate, not a measurement. Read Limitations before quoting a number.

---

Try it in two minutes

Requires Docker. Nothing else, no build toolchain, no PostgreSQL on your host.

git clone https://github.com/green-coding-solutions/planet
cd planet
make demo

make demo builds a PostgreSQL 18.4 image with the extension in it, starts it, and walks a psql session through the whole interface: the per-statement INFO report, planet_last(), EXPLAIN (ANALYZE, PLANET) in text and JSON, a two-plan comparison, and the query fingerprint a stored decision is keyed on. The script it runs is docker/demo.sql; it is plain SQL and runs against your own server just as well.

make test    # the extension's regression suite, against that server
make psql    # a shell on it
make down    # stop  (make clean also drops the data volume)

Install into a PostgreSQL you already run

Requires PostgreSQL 18 and its server headers. The build fails loudly on anything older: ExecutorRun_hook lost its execute_once argument in 18, the EXPLAIN extension hooks did not exist before it, and planet.c #errors on older headers.

make install PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config    # usually sudo

Then in a database:

CREATE EXTENSION planet;

make installcheck runs the regression suite against a running server.

---

Using it

Start every session with LOAD 'planet';

LOAD 'planet';              -- installs the hooks, the GUCs and EXPLAIN (PLANET)
SET planet.report = on;     -- one INFO line after every top-level statement

The LOAD is not a formality. The SQL functions do dlopen the module on first call, but the executor hooks only exist from that moment on, so without it the first planet_last() in a session returns nothing and the query you actually wanted to measure has already run unobserved. SET planet.report needs it too: the planet.* GUCs do not exist until the module is in your session.

To have every session start that way, set session_preload_libraries = 'planet' in postgresql.conf. Prefer that over shared_preload_libraries, which no reload can undo. Do not preload it at all while running eval/bench_overhead.py, whose baseline arm needs a session where the hooks are absent.

One INFO line per statement

SET planet.report = on;
SELECT count(*) FROM orders WHERE placed_at > now() - interval '7 days';

INFO:  PLANET energy=11.1926 J (compute=11.1926 awake=0 io=0 wal=0)
       cpu=0.746173s wall=0.197735s blks_read=0 blks_written=0 wal=0B

The same reading as a row

SELECT sum(amount) FROM orders WHERE customer_id < 100;
SELECT * FROM planet_last();

 energy_j | compute_j |  io_j   | wal_j | awake_j | cpu_seconds | wall_seconds | blks_read | ...
----------+-----------+---------+-------+---------+-------------+--------------+-----------+----
  0.01443 |   0.01419 | 0.00024 |     0 |       0 |    0.000946 |     0.000946 |         3 | ...

planet_last() reads the previous top-level statement, and it is itself a statement, so it replaces the reading once it finishes. Take every field you need in one call. To capture a reading without destroying it, put the call inside a statement that stores it:

SELECT count(*) FROM orders WHERE customer_id = 42;
CREATE TEMP TABLE reading AS SELECT 'index scan' AS plan, * FROM planet_last();

Scalar getters exist too (planet_last_energy_joules(), planet_last_cpu_seconds(), planet_last_queryid(), and so on), and planet_reset() clears the session's state.

---

EXPLAIN (ANALYZE, PLANET)

This is where the number belongs: next to the plan that earned it. The other two interfaces put the reading in the message stream or in a row, neither of which sits beside the plan, and an energy comparison of two plans wants exactly that.

EXPLAIN (ANALYZE, PLANET) SELECT count(*) FROM orders WHERE customer_id = 42;

ANALYZE is required

EXPLAIN (PLANET) SELECT count(*) FROM orders;

ERROR:  EXPLAIN option PLANET requires ANALYZE
HINT:   PLANET reports the energy a statement did use, not the energy the
        planner expects it to use, so the statement has to run.

PLANET measures; it does not predict. Its inputs are CPU seconds, blocks and WAL bytes, none of which exist until the statement has run, and a plain EXPLAIN runs nothing. Deriving joules from the planner's cost estimate instead would be a different claim from the one the coefficients were fit for, and is deliberately not offered. Core PostgreSQL applies the same rule, for the same reason, to WAL, TIMING and SERIALIZE.

The option is likewise refused when planet.enabled is off, at parse time rather than after wasting an execution.

What the section means

Property	Meaning
Energy	the whole statement's estimated dynamic energy, in joules
Compute Energy	planet.cpu_active_watts x CPU seconds
Awake Energy	planet.awake_watts x wall seconds
I/O Energy	reads x planet.joules_per_read + writes x planet.joules_per_write
WAL Energy	WAL bytes x planet.joules_per_wal_byte
CPU Time, Wall Time, Blocks Read, Blocks Written, WAL Bytes	the counters the model was evaluated on

Semantics worth knowing:

• Per query, not per node. The section reports the whole statement. Per-node energy would need per-node CPU attribution, and PLANET's compute term comes from getrusage() over the whole backend. That is a modelling question, not a plumbing one, and it is future work.
• One statement, one reading. The section is printed from explain_per_plan_hook, which fires before ExecutorEnd. PLANET settles the statement there and ExecutorEnd finds it already settled, so the number in the plan is bit-identical to the one planet_last() then returns. If each took its own measurement, the second would be slightly larger by the CPU spent formatting the plan.
• Where it appears. After the plan, the planning summary, and the trigger and JIT blocks; before Execution Time.
• All four formats. TEXT, JSON, XML, YAML. Text is hand-formatted to carry the same fields in the same order as the planet.report INFO line, so one grep pattern finds both. The structured formats carry joules to 9 decimals, because a query costing 1e-3 J is routine and 3 decimals would quantise away the difference between two plans.
• It takes a boolean. EXPLAIN (ANALYZE, PLANET off) ... is spellable, so a harness can switch it off in place.
• Nested EXPLAINs abstain. An EXPLAIN (ANALYZE, PLANET) running inside another statement is not closing out a snapshot of its own, and prints PLANET: not measured rather than reporting its caller's energy.

"PLANET": {
  "Energy": 0.003120000,
  "Compute Energy": 0.003120000,
  "Awake Energy": 0.000000000,
  "I/O Energy": 0.000000000,
  "WAL Energy": 0.000000000,
  "CPU Time": 0.000208,
  "Wall Time": 0.000208,
  "Blocks Read": 0,
  "Blocks Written": 0,
  "WAL Bytes": 0
}

---

Comparing two alternatives

This is what PLANET is for, and it is more than running each side once. Two readings give an ordering; whether that ordering is real depends on how well the coefficients are pinned down.

eval/compare_energy.py does the whole procedure. It runs both alternatives in randomised paired blocks, captures planet_last() after each execution, then re-fits the coefficients on 4,000 bootstrap resamples of the original calibration sweep, applies each refit to both alternatives' median counters, and resamples the fit's relative residual on top. That yields a 95% interval on the percentage difference.

It recommends an alternative only when the whole interval lies on one side of zero. Otherwise it abstains.

python3 eval/compare_energy.py -d bench \
    -q 'SELECT count(*) FROM orders WHERE customer_id = 42' \
    -A 'SET enable_seqscan = off'                       --label-a index \
    -B 'SET enable_indexscan = off; SET enable_bitmapscan = off' --label-b seq \
    --sweep calibration/sweep.csv

  variant      energy J      wall s       cpu s   blks_read     n
  index         0.00129  8.5792e-05     8.6e-05           0    15
  seq          0.864315   0.0576242    0.057621           0    15

  point estimate: seq uses +66901.2% energy relative to index  (670.0x)
  break-even idle power P* = -15.0 W: no crossover. index is both faster
  and lower in dynamic energy, so it also has the lower total energy at any
  idle draw.

  95% interval on that difference: [+60406.4%, +74305.9%]

  RECOMMENDATION: index. The whole interval says seq costs more energy.

A near-tie gets the other answer, which is the point:

  95% interval on that difference: [-14.4%, +5.1%]

  NO RECOMMENDATION. The interval includes zero, so equal energy use
  remains possible.

Across the paper's 16 metered contrasts the rule named a winner in 8; paired physical metering confirmed 7, could not resolve 1, and refuted none. On 6 contrasts held out of the calibration entirely it named 2 winners, both confirmed. It abstains often. That is the trade it makes, and it is why a recommendation it does make is worth acting on.

Under Docker: make compare ARGS="-q '...' -A '...' -B '...'".

Latency, energy, and planner cost rank plans differently

Three real results from the paper, all measured with an AC wall meter:

• Of three scan plans at 20% selectivity, the planner's most expensive plan used the least energy. Cache residency causes the inversion, so no fixed rescaling of planner cost can correct it.
• wal_compression = lz4 changed dynamic energy measurably while planner cost stayed identical across the setting.
• A 4-worker parallel plan finished sooner but burned more total CPU. PLANET's interval includes zero, so the rule abstains on dynamic energy, and the total-energy answer is still clear.

That last case is why the tool prints P*, the break-even idle power:

P* = -(E_dyn(B) - E_dyn(A)) / (T(B) - T(A))

PLANET measures energy above idle. A plan that finishes sooner also stops the machine idling sooner. P* is the machine idle draw at which the two total energies cross: above it the faster plan wins overall, below it the lower-dynamic-energy one does. Comparing P* with your machine's real idle draw turns "uses less dynamic energy" into "costs less on the electricity bill", which are different claims.

Acting on a comparison

A comparison is offline analysis. The verdict store gives its result somewhere to live inside the engine, so the analysis is done once instead of on every planning pass. A verdict is a tested GUC overlay recorded against a statement's normalised fingerprint:

SELECT count(*) FROM orders WHERE customer_id = 42;
SELECT planet_verdict_record(
    planet_last_queryid(),
    'enable_seqscan=off',
    expected_j => 0.00129,     -- what the winning variant measured
    baseline_j => 0.864315,    -- what it displaced
    cost_lo    => 80000,       -- the plan-cost range it was established in
    cost_hi    => 130000);

SET planet.apply_verdicts = on;    -- off by default; nothing applies until then
SELECT * FROM planet_verdicts();

The rules that keep a stored verdict honest:

• Inert by default. planet.apply_verdicts is off, so PLANET only measures unless you say otherwise. It was off for every measurement in the paper.
• Domain checked. With finite cost bounds recorded and planet.verdict_domain_check on, the statement is planned unsteered first and the overlay applies only inside that cost range. A verdict with open bounds asserts it holds at every size and selectivity, which is rarely true.
• Withdrawn on contradiction. If newly planned steered executions come in above baseline_j planet.verdict_revoke_after times in a row, the verdict is revoked. Any non-contradicting run resets the count. Re-executing a cached plan never re-enters the planner and so cannot count.
• Administrative. Recording one changes which plan other sessions get, so the recording functions are revoked from PUBLIC. Reading the store is not.
• Volatile. Verdicts live in shared memory and are lost at restart, on purpose: they rest on coefficients valid for one hardware, frequency and PostgreSQL configuration, and a restart that changed any of those should not silently resurrect conclusions drawn under the old one. planet_verdicts_save() and planet_verdicts_load() are the explicit round trip.

A verdict is local evidence, not an optimizer rule. It is a setting overlay for one normalised statement on one platform. The optimizer's cost model and search are untouched.

---

The model

Five coefficients over five counters:

E = cpu_active_watts   x cpu_seconds        -- busy-core power
  + awake_watts        x wall_seconds       -- power for being out of deep idle
  + joules_per_read    x blks_read
  + joules_per_write   x blks_written
  + joules_per_wal_byte x wal_bytes

Every term is an increment above the machine's idle draw. The counters were chosen because they are observed for each completed statement, available without privileged access, and attributable under concurrency. Machine sensors and RAPL cannot divide energy among queries; hardware performance counters are socket-wide and often privileged; planner costs predict work rather than observe it.

The awake_watts term earns its place: removing it raises within-domain error on the metered server from 7.5% to 22.8%. Direct I/O separates wall time from CPU time, which is what makes the term identifiable. Non-negative least squares drives it to zero on machines where it does not exist, recovering a four-term model (use fit_model.py --four-term where the two are collinear).

Calibrating for your machine

Out of the box PLANET ships placeholder coefficients. They are enough to rank two plans that differ a lot; they are not your hardware. Fit your own, on an otherwise idle host, as a database superuser:

pip install -r requirements.txt          # numpy, scipy; matplotlib for figures
sudo chmod a+r /sys/class/powercap/intel-rapl:*/energy_uj
cd calibration
python3 rapl.py --check                              # is a meter readable?
python3 collect_sweep.py -d calib -o sweep.csv       # ~15 min, 7 workload families
python3 check_sweep.py sweep.csv                     # PASS / FAIL gate
python3 fit_model.py sweep.csv -o ../config/coefficients.json
python3 validate_model.py sweep.csv                  # held-out accuracy

fit_model.py prints the SET statements to paste into psql, or to promote to ALTER SYSTEM SET / postgresql.conf once you are happy with them. On the containerised server, make coefficients pushes config/coefficients.json in for you.

Linux RAPL is the default meter; an AC wall meter is supported through the same interface (gmt_meter.py). A wall meter is the better reference, because the target includes storage and platform energy that CPU-side counters never see: on the paper's server the wall meter attributes 155 µJ per block read where RAPL attributes 70 µJ, and 15.4 nJ per WAL byte against RAPL's 3.2 nJ. Refitting to RAPL preserves every plan-comparison result but shrinks the measured differences by 18-50%.

Keep sweep.csv. It is not just an intermediate: compare_energy.py needs it to derive the uncertainty behind a recommendation.

Recalibrate after any change to hardware, CPU frequency policy, or PostgreSQL configuration. Coefficients do not transfer between machines: applying the server's model to the laptop gives 178% error, against 5.8% after recalibration. Rankings survive the transfer far better than absolute joules do (Spearman rho 0.96).

Full detail, including what to do when RAPL is unavailable and why the sweep is shaped the way it is: calibration/README.md.

Configuration

Every setting is a planet.* GUC and exists only in sessions that have LOADed the module.

GUC	default	meaning
planet.enabled	on	master switch (superuser)
planet.report	off	emit an INFO line per top-level statement
planet.cpu_active_watts	15.0	dynamic power per busy core, W (calibrated)
planet.awake_watts	0.0	load-independent power while a query runs, W (calibrated)
planet.joules_per_read	8e-5	energy per block read, J (calibrated)
planet.joules_per_write	1.6e-4	energy per block written, J (calibrated)
planet.joules_per_wal_byte	2e-8	energy per WAL byte, J (calibrated)
planet.apply_verdicts	off	re-apply recorded verdicts at plan time
planet.verdict_domain_check	on	plan unsteered first, apply only inside the recorded cost range
planet.verdict_revoke_after	3	consecutive contradictions before a verdict is withdrawn

The five calibrated ones are the output of fit_model.py. See config/coefficients.example.json.

What is and is not measured

• Top-level statements only. Nested queries fold into their caller.
• Executor work only. Parsing and planning are outside the window, as is background activity. The awake term can absorb an in-executor stall, such as result delivery blocked on a slow client.
• Completed statements only. Failed and cancelled statements are never charged, and never replace the previous reading.
• Plain EXPLAIN and EXPLAIN (GENERIC_PLAN) execute nothing and leave the previous reading intact. EXPLAIN ANALYZE really runs, so it is captured.
• Parallel workers are included. Each measured statement claims one of 128 shared worker-CPU slots and workers publish their CPU through the leader's slot. Once every slot is occupied, a further parallel leader omits worker CPU. Block and WAL counters are aggregated by PostgreSQL regardless.
• Deferred work is excluded. Checkpoints, autovacuum, background WAL flush, network, and cooling all fall outside the attribution boundary. The paper has a WAL case where a checkpoint outside the statement window changes the answer by more than the statement itself.
• Repeated reads are charged every time they occur, whether or not the block came from the OS page cache. PostgreSQL reports device and temporary-file reads in one counter, so joules_per_read is a traffic-weighted average.

Limitations

• Estimates, not measurements. 7.5% mean absolute percentage error against an AC wall meter on the calibrated server (5.8% on the laptop), inside the calibrated domain. That is 1.4 to 1.9 times the repeated-measurement variability of the queries themselves, which is the floor any model is judged against.
• The sweep has to cover what you run. Withholding a whole workload family from the fit and predicting it gives a median error of about 12%, but the pure-CPU family reaches 57%, because it is the family that separates cpu_active_watts from awake_watts. Calibrate over the execution paths you actually deploy.
• Per query, not per plan node.
• Coefficients do not transfer between machines, or between CPU frequency settings on one machine.
• Concurrency has a ceiling. Independent per-query estimates sum to within 29.3% of the machine's metered energy up to one query per physical core. Past that they increasingly overestimate, because the awake term charges each query for its full elapsed time even while it shares a core.
• Dynamic energy only. No share of the machine's idle draw is allocated; that is what P* is for. Embodied hardware impact, retained data and carbon intensity are out of scope.

Layout

Directory	What
extension/	the C extension, its SQL interface, and its regression suite
calibration/	meter readers, the sweep, the fit, and its validation
eval/	comparison guard, overhead benchmark, plan and concurrency experiments
config/	annotated example coefficients
docker/	PostgreSQL 18.4 with the extension built in, plus the demo

Reproducing the paper's experiments

The measured results need an otherwise idle Linux host with a real meter. See eval/README.md for the exact commands, and docker/README.md for the containerised rig.

cd docker
make up
make dataset SCALE=20000000     # the paper's scale, ~3 GB
make overhead ARGS="--mode ro -c 6 -j 3 --rounds 10 --time 60"
make pilot && make plot

License

AGPL-3.0-or-later. See LICENSE.

Built by Green Coding Solutions.