demo.sql

-- PLANET guided demo --- five minutes, one psql session, no meter required.
--
--     cd docker && make demo
--
-- Everything here works against a stock PostgreSQL 18 with the extension
-- installed; the Makefile target only supplies the container.  Run it by hand
-- against your own server with:
--
--     psql -d yourdb -f docker/demo.sql
--
-- The numbers you see are joules for THIS machine, and are only as good as the
-- coefficients in force.  Out of the box those are placeholders (see the banner
-- the demo prints); `cd calibration && ...` replaces them with a fit against a
-- real meter.  Uncalibrated, the ordering of two plans is still informative and
-- the absolute joules are not.

\set ON_ERROR_STOP on
\timing off
\pset pager off

-- LOAD installs the executor hooks, the planet.* GUCs, and EXPLAIN (PLANET)
-- into this session.  It is per-session and it is not optional: without it the
-- statement you wanted to measure runs unhooked.
LOAD 'planet';

\echo
\echo '=============================================================='
\echo ' 0. The model in force'
\echo '=============================================================='
\echo

-- Five coefficients, fit once per machine.  energy =
--   cpu_active_watts * cpu_s  +  awake_watts * wall_s
--   + joules_per_read * blks_read + joules_per_write * blks_written
--   + joules_per_wal_byte * wal_bytes
SELECT name, setting, short_desc
  FROM pg_settings
 WHERE name IN ('planet.cpu_active_watts', 'planet.awake_watts',
                'planet.joules_per_read', 'planet.joules_per_write',
                'planet.joules_per_wal_byte')
 ORDER BY name;

SELECT CASE
    WHEN current_setting('planet.cpu_active_watts')::float8 = 15.0
     AND current_setting('planet.awake_watts')::float8 = 0.0
    THEN 'UNCALIBRATED: these are the shipped placeholder coefficients. '
         'Joules below are illustrative. See calibration/README.md.'
    ELSE 'Calibrated coefficients are in force.'
  END AS status;

\echo
\echo '=============================================================='
\echo ' 1. A dataset to measure'
\echo '=============================================================='
\echo

DROP TABLE IF EXISTS demo_orders;

CREATE TABLE demo_orders AS
SELECT g                              AS order_id,
       (g % 50000)                    AS customer_id,
       (random() * 1000)::numeric(9,2) AS amount,
       now() - (g % 365) * interval '1 day' AS placed_at,
       md5(g::text)                   AS payload
  FROM generate_series(1, 2000000) g;

CREATE INDEX demo_orders_customer_idx ON demo_orders (customer_id);
ANALYZE demo_orders;

\echo
\echo '=============================================================='
\echo ' 2. planet.report --- one INFO line per top-level statement'
\echo '=============================================================='
\echo

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

\echo
\echo '=============================================================='
\echo ' 3. planet_last() --- the same reading as a row'
\echo '=============================================================='
\echo

SELECT sum(amount) FROM demo_orders WHERE customer_id < 100;

-- planet_last() reads the PREVIOUS top-level statement.  This SELECT is itself
-- a statement and will replace the reading once it finishes, so take every
-- field you need in one call.
\x on
SELECT * FROM planet_last();
\x off

\echo
\echo '=============================================================='
\echo ' 4. EXPLAIN (ANALYZE, PLANET) --- joules next to the plan'
\echo '=============================================================='
\echo

-- ANALYZE is mandatory.  PLANET measures counters that do not exist until the
-- statement has run; a plain EXPLAIN runs nothing.  Try it without and the
-- option is refused with a hint, rather than reporting a misleading zero.
EXPLAIN (ANALYZE, PLANET, BUFFERS, COSTS off)
SELECT count(*) FROM demo_orders WHERE customer_id = 42;

\echo
\echo '-- ... and in JSON, for a harness:'
\echo

EXPLAIN (ANALYZE, PLANET, TIMING off, COSTS off, FORMAT JSON)
SELECT count(*) FROM demo_orders WHERE customer_id = 42;

\echo
\echo '=============================================================='
\echo ' 5. Comparing two plans --- what PLANET is for'
\echo '=============================================================='
\echo
\echo '-- Same query, two access paths.  Each side runs once to warm the'
\echo '-- cache, then once for the record.  A cold first touch would swamp'
\echo '-- anything the model has to say.'
\echo

SET max_parallel_workers_per_gather = 0;

-- ---- index scan ----------------------------------------------------------
SET enable_seqscan = off;
SELECT count(*) FROM demo_orders WHERE customer_id = 42;   -- warmup
SELECT count(*) FROM demo_orders WHERE customer_id = 42;   -- the one we keep
-- The capture is a CREATE TABLE AS, not a bare SELECT: planet_last() is
-- evaluated while the previous statement's reading is still current.
CREATE TEMP TABLE demo_idx AS
    SELECT 'index scan' AS plan, * FROM planet_last();
RESET enable_seqscan;

-- ---- sequential scan -----------------------------------------------------
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM demo_orders WHERE customer_id = 42;   -- warmup
SELECT count(*) FROM demo_orders WHERE customer_id = 42;   -- the one we keep
CREATE TEMP TABLE demo_seq AS
    SELECT 'seq scan' AS plan, * FROM planet_last();
RESET enable_indexscan;
RESET enable_bitmapscan;

SELECT plan,
       round(energy_j::numeric, 6)     AS energy_j,
       round(compute_j::numeric, 6)    AS compute_j,
       round(io_j::numeric, 6)         AS io_j,
       round(cpu_seconds::numeric, 6)  AS cpu_s,
       round(wall_seconds::numeric, 6) AS wall_s,
       blks_read
  FROM (SELECT * FROM demo_idx UNION ALL SELECT * FROM demo_seq) r
 ORDER BY energy_j;

\echo
\echo '-- The cheaper plan is the first row. Two readings are a point'
\echo '-- ordering, not yet a decision: eval/compare_energy.py turns repeated'
\echo '-- readings plus the calibration sweep into an interval, and only'
\echo '-- recommends when that interval excludes zero.'
\echo

\echo
\echo '=============================================================='
\echo ' 6. The query fingerprint --- how a decision is keyed'
\echo '=============================================================='
\echo

SELECT count(*) FROM demo_orders WHERE customer_id = 42;
CREATE TEMP TABLE demo_qid AS SELECT planet_last_queryid() AS first_queryid;

SELECT count(*) FROM demo_orders WHERE customer_id = 4242;
SELECT first_queryid,
       planet_last_queryid()                 AS second_queryid,
       first_queryid = planet_last_queryid() AS same_fingerprint
  FROM demo_qid;

\echo
\echo '-- Same shape, different constant, same fingerprint --- which is what'
\echo '-- makes a stored verdict apply to the statement rather than to one'
\echo '-- literal. See README.md, "Acting on a comparison".'
\echo

DROP TABLE demo_orders;

\echo
\echo 'demo complete.'
\echo