Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- to be calculated AFTER INSERT via TRIGGER
    mem_lag REAL,    -- to be calculated AFTER INSERT via TRIGGER
    util_diff REAL,  -- to be calculated AFTER INSERT via TRIGGER
    mem_diff REAL,/*a comma was missing here*/    -- to be calculated AFTER INSERT via TRIGGER
    util_change TEXT -- to be calculated AFTER INSERT via TRIGGER
);

CREATE TABLE


ALTER TABLE myTable ALTER COLUMN datetime TYPE timestamptz
  USING (datetime::timestamptz);
CREATE INDEX dtidx ON myTable (datetime) 
  INCLUDE (cputil, memfree)
  WITH (fillfactor=100);
--ALTER TABLE myTable CLUSTER ON dtidx;

ALTER TABLE



CREATE INDEX


vacuum analyze myTable;

VACUUM


CREATE OR REPLACE FUNCTION fn_calculate_columns_after_insert()--missing parentheses here
    RETURNS TRIGGER LANGUAGE PLPGSQL AS $f$
BEGIN
    SELECT     cputil, memfree 
    INTO NEW.util_lag, NEW.mem_lag
    FROM myTable 
    WHERE datetime < NEW.datetime
    ORDER BY datetime DESC
    LIMIT 1;

    NEW.util_diff := NEW.cputil  - NEW.util_lag;
    NEW.mem_diff  := NEW.memfree - NEW.mem_lag;
    --the three cases explicitly say what you want and when
    --ELSE no longer assumes the same case for 0 and null
    --null is possible if there were any nulls involved earlier
    IF NEW.util_diff > 0 THEN
        NEW.util_change := 'Up';
    ELSIF NEW.util_diff < 0 THEN
        NEW.util_change := 'Down';
    ELSIF NEW.util_diff = 0 THEN
        NEW.util_change := '';
    END IF;--this was missing
    RETURN NEW;
END;--This semicolon is optional because it's immediately followed by closing dollar qoutes.
$f$;--This semicolon was missing. It is not optional, because it ends the whole `create function` statement

CREATE TRIGGER tr_fill_calculated_columns
    BEFORE INSERT ON myTable
    FOR EACH ROW
    EXECUTE FUNCTION fn_calculate_columns_after_insert();

CREATE FUNCTION



CREATE TRIGGER


set datestyle=ISO, YMD;
insert into myTable(datetime,sys_id,cputil,memfree,sessnum)values
 ('2019/05/03 08:06:14',100,0.57,0.51,47)
,('2019/05/03 08:11:14',100,0.47,0.62,43)
,('2019/05/03 08:16:14',100,0.56,0.57,62) returning *;

SET




datetime
sys_id
cputil
memfree
sessnum
util_lag
mem_lag
util_diff
mem_diff
util_change


2019-05-03 08:06:14+00
100
0.57
0.51
47
null
null
null
null
null

2019-05-03 08:11:14+00
100
0.47
0.62
43
0.57
0.51
-0.099999994
0.110000014
Down

2019-05-03 08:16:14+00
100
0.56
0.57
62
0.47
0.62
0.09
-0.050000012
Up



INSERT 0 3


select setseed(.42);

explain analyze verbose
insert into myTable(datetime,sys_id,cputil,memfree,sessnum)
select datetime,100 as sys_id,
       round(random()::numeric,2) as cputil,
       round(random()::numeric,2) as memfree,
       100*random() as sessnum
from generate_series('2019/05/03 08:16:14'::timestamptz,
                     '2021/06/03 00:00:00', '5 minutes')_(datetime);


setseed






SELECT 1




QUERY PLAN


Insert on public.mytable  (cost=0.00..37.50 rows=0 width=0) (actual time=13859.346..13859.347 rows=0 loops=1)

  ->  Subquery Scan on "*SELECT*"  (cost=0.00..37.50 rows=1000 width=72) (actual time=46.166..2252.260 rows=219357 loops=1)

        Output: "*SELECT*".datetime, "*SELECT*".sys_id, "*SELECT*".cputil, "*SELECT*".memfree, "*SELECT*".sessnum, NULL::real, NULL::real, NULL::real, NULL::real, NULL::text

        ->  Function Scan on pg_catalog.generate_series _  (cost=0.00..30.00 rows=1000 width=84) (actual time=46.160..1755.709 rows=219357 loops=1)

              Output: _.datetime, 100, round((random())::numeric, 2), round((random())::numeric, 2), ('100'::double precision * random())

              Function Call: generate_series('2019-05-03 08:16:14+00'::timestamp with time zone, '2021-06-03 00:00:00+00'::timestamp with time zone, '00:05:00'::interval)

Planning Time: 0.068 ms

Trigger tr_fill_calculated_columns: time=9036.443 calls=219357

Execution Time: 13860.206 ms



EXPLAIN


explain analyze verbose 
SELECT     cputil, memfree 
--    INTO NEW.util_lag, NEW.mem_lag
FROM myTable 
WHERE datetime < '2021/09/03 00:00:00'
ORDER BY datetime DESC
LIMIT 1;


QUERY PLAN


Limit  (cost=0.42..0.60 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=1)

  Output: cputil, memfree, datetime

  ->  Index Only Scan Backward using dtidx on public.mytable  (cost=0.42..10193.52 rows=55377 width=16) (actual time=0.014..0.015 rows=1 loops=1)

        Output: cputil, memfree, datetime

        Index Cond: (mytable.datetime < '2021-09-03 00:00:00+00'::timestamp with time zone)

        Heap Fetches: 1

Planning Time: 0.084 ms

Execution Time: 0.030 ms



EXPLAIN


explain analyze verbose 
SELECT     cputil, memfree 
--    INTO NEW.util_lag, NEW.mem_lag
FROM myTable 
--WHERE datetime < NEW.datetime
ORDER BY datetime DESC
LIMIT 1;


QUERY PLAN


Limit  (cost=0.42..0.50 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  Output: cputil, memfree, datetime

  ->  Index Only Scan Backward using dtidx on public.mytable  (cost=0.42..13756.38 rows=166131 width=16) (actual time=0.007..0.008 rows=1 loops=1)

        Output: cputil, memfree, datetime

        Heap Fetches: 1

Planning Time: 0.040 ms

Execution Time: 0.019 ms



EXPLAIN
datetime	sys_id	cputil	memfree	sessnum	util_lag	mem_lag	util_diff	mem_diff	util_change
2019-05-03 08:06:14+00	100	0.57	0.51	47	null	null	null	null	null
2019-05-03 08:11:14+00	100	0.47	0.62	43	0.57	0.51	-0.099999994	0.110000014	Down
2019-05-03 08:16:14+00	100	0.56	0.57	62	0.47	0.62	0.09	-0.050000012	Up