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?.

--https://stackoverflow.com/q/78204363/5298879
--SQL Query to average multiple data and compare with one data point to check offset

--I am new to SQL so please bear with me. 
--In one of my tables I have a series of inverter voltage values, 
--written every minute. I am displaying the data on GRAFANA 
--and would like to find anomalous points, if any. 
--A really simple solution for me now is to get the average of all voltages 
--at a specific time, and see whether 1 value is off by "a lot".

--Some background: Using postresql, and the columns "timestamp", "value", "name" 
--name is the identifier and right now the inverters are in the format 
--'INVX_INVERTER_SG_Branch VoltageY' 
--where Y = {1,3,5,7,9,11,13,15,17,19,21,23} and X = {01-18}.
select setseed(.42);

create table st_values("timestamp", "value", "name") as 
select 'today'::timestamp+n*'1 minute'::interval,
       (2*random())^3,
       format('INV%s_INVERTER_SG_Branch Voltage%s',lpad(x::text,2,'0'),y)
from generate_series(1,18,1)x,
     generate_series(1,23,2)y,
     generate_series(1,700)n;

select * from st_values tablesample bernoulli(.42)repeatable(.42)
order by "timestamp" limit 16;
create index tnv_idx on st_values ("timestamp","name")
  include("value")
  with(fillfactor=100);


setseed






SELECT 1



SELECT 151200




timestamp
value
name


2024-03-22 00:02:00
6.707207629391543
INV10_INVERTER_SG_Branch Voltage15

2024-03-22 00:04:00
0.2165854378283646
INV16_INVERTER_SG_Branch Voltage5

2024-03-22 00:06:00
0.0012182924942929373
INV11_INVERTER_SG_Branch Voltage9

2024-03-22 00:07:00
0.6768534194102499
INV09_INVERTER_SG_Branch Voltage19

2024-03-22 00:09:00
0.2907072230437511
INV06_INVERTER_SG_Branch Voltage3

2024-03-22 00:09:00
1.3618524145407915
INV03_INVERTER_SG_Branch Voltage23

2024-03-22 00:13:00
0.03309596412063995
INV09_INVERTER_SG_Branch Voltage23

2024-03-22 00:14:00
0.24989200485469099
INV12_INVERTER_SG_Branch Voltage5

2024-03-22 00:14:00
0.5135056910779185
INV01_INVERTER_SG_Branch Voltage11

2024-03-22 00:16:00
0.13517425939443212
INV08_INVERTER_SG_Branch Voltage9

2024-03-22 00:20:00
2.657070883085778
INV18_INVERTER_SG_Branch Voltage13

2024-03-22 00:22:00
3.592830362401637
INV15_INVERTER_SG_Branch Voltage3

2024-03-22 00:26:00
3.382074211594285
INV15_INVERTER_SG_Branch Voltage13

2024-03-22 00:28:00
0.10588884732543108
INV16_INVERTER_SG_Branch Voltage3

2024-03-22 00:28:00
0.96926796908084
INV09_INVERTER_SG_Branch Voltage5

2024-03-22 00:28:00
0.09788700606805915
INV02_INVERTER_SG_Branch Voltage1



SELECT 16



CREATE INDEX


cluster st_values using tnv_idx;

CLUSTER


vacuum analyze st_values;

VACUUM


--The first temporary query works as expected, 
--it produces a table of timestamps and average values of all combinations at that time.
--In the second SELECT query I am getting bullshit values. 
--There is no other data with name containing 'SG_Branch Voltage' on the DB Table. 
--Our friendly chatgpt is spitting garbage.

--Ideally the result will show me timestamps and values for anomalous points. 
--I shall then plot those points on the graph. 
--This will simply be an indicator of the point being not as expected.
explain analyze verbose
WITH average AS (
    SELECT "timestamp",
            AVG(value) AS avg_value
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    GROUP BY "timestamp" )
SELECT v."timestamp",
       v.value,
       av.avg_value,
       ABS(v.value - av.avg_value) / av.avg_value as deviation
FROM st_values v
JOIN average av USING("timestamp")
WHERE v."timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
  AND v."name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
  AND ABS(v.value - av.avg_value) / av.avg_value > 0.3 
ORDER BY v."timestamp"--, deviation desc
--LIMIT 24;
--The code above is (semi?) not working. 
--I am not sure how to debug this. 
--This yields points that are truly anomalous (i.e. lie on one of my lines) 
--but some that are (fake?) not lying on any lines.


QUERY PLAN


Nested Loop  (cost=5121.09..13301.51 rows=33779 width=32) (actual time=87.917..138.549 rows=88811 loops=1)

  Output: v."timestamp", v.value, (avg(st_values.value)), (abs((v.value - (avg(st_values.value)))) / (avg(st_values.value)))

  ->  Finalize GroupAggregate  (cost=5120.66..5213.41 rows=700 width=16) (actual time=87.872..88.735 rows=471 loops=1)

        Output: st_values."timestamp", avg(st_values.value)

        Group Key: st_values."timestamp"

        ->  Gather Merge  (cost=5120.66..5201.16 rows=700 width=40) (actual time=87.860..88.218 rows=490 loops=1)

              Output: st_values."timestamp", (PARTIAL avg(st_values.value))

              Workers Planned: 1

              Workers Launched: 1

              ->  Sort  (cost=4120.65..4122.40 rows=700 width=40) (actual time=82.137..82.178 rows=245 loops=2)

                    Output: st_values."timestamp", (PARTIAL avg(st_values.value))

                    Sort Key: st_values."timestamp"

                    Sort Method: quicksort  Memory: 45kB

                    Worker 0:  actual time=81.837..81.850 rows=234 loops=1

                      Sort Method: quicksort  Memory: 43kB

                    ->  Partial HashAggregate  (cost=4080.57..4087.57 rows=700 width=40) (actual time=82.014..82.065 rows=245 loops=2)

                          Output: st_values."timestamp", PARTIAL avg(st_values.value)

                          Group Key: st_values."timestamp"

                          Batches: 1  Memory Usage: 105kB

                          Worker 0:  actual time=81.707..81.752 rows=234 loops=1

                            Batches: 1  Memory Usage: 73kB

                          ->  Parallel Seq Scan on public.st_values  (cost=0.00..3782.53 rows=59609 width=16) (actual time=0.013..68.271 rows=50868 loops=2)

                                Output: st_values."timestamp", st_values.value, st_values.name

                                Filter: ((st_values.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text) AND (st_values."timestamp" <= now()) AND (st_values."timestamp" >= (now() - '1 day'::interval)))

                                Rows Removed by Filter: 24732

                                Worker 0:  actual time=0.012..69.206 rows=48289 loops=1

  ->  Index Only Scan using tnv_idx on public.st_values v  (cost=0.43..10.70 rows=48 width=16) (actual time=0.005..0.077 rows=189 loops=471)

        Output: v."timestamp", v.name, v.value

        Index Cond: ((v."timestamp" = st_values."timestamp") AND (v."timestamp" >= (now() - '1 day'::interval)) AND (v."timestamp" <= now()))

        Filter: ((v.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text) AND ((abs((v.value - (avg(st_values.value)))) / (avg(st_values.value))) > '0.3'::double precision))

        Rows Removed by Filter: 27

        Heap Fetches: 0

Planning Time: 1.246 ms

Execution Time: 141.974 ms



EXPLAIN


explain analyze verbose
SELECT * FROM (
    SELECT "timestamp",
           value,
           avg(value)over w1 as avg_value,
           ABS(value - avg(value)over w1) / avg(value)over w1 as deviation
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    WINDOW w1 AS (PARTITION BY "timestamp") ) AS subq
WHERE .3 < deviation
ORDER BY "timestamp"--, deviation desc
--LIMIT 24;


QUERY PLAN


Subquery Scan on subq  (cost=0.43..9311.53 rows=33779 width=32) (actual time=0.183..95.354 rows=88811 loops=1)

  Output: subq."timestamp", subq.value, subq.avg_value, subq.deviation

  Filter: ('0.3'::double precision < subq.deviation)

  Rows Removed by Filter: 12925

  ->  WindowAgg  (cost=0.43..8044.82 rows=101337 width=32) (actual time=0.183..86.255 rows=101736 loops=1)

        Output: st_values."timestamp", st_values.value, avg(st_values.value) OVER (?), (abs((st_values.value - avg(st_values.value) OVER (?))) / avg(st_values.value) OVER (?))

        ->  Index Only Scan using tnv_idx on public.st_values  (cost=0.43..5764.74 rows=101337 width=16) (actual time=0.039..40.676 rows=101736 loops=1)

              Output: st_values."timestamp", st_values.value

              Index Cond: ((st_values."timestamp" >= (now() - '1 day'::interval)) AND (st_values."timestamp" <= now()))

              Filter: (st_values.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text)

              Heap Fetches: 0

Planning Time: 0.193 ms

Execution Time: 98.462 ms



EXPLAIN

timestamp	value	name
2024-03-22 00:02:00	6.707207629391543	INV10_INVERTER_SG_Branch Voltage15
2024-03-22 00:04:00	0.2165854378283646	INV16_INVERTER_SG_Branch Voltage5
2024-03-22 00:06:00	0.0012182924942929373	INV11_INVERTER_SG_Branch Voltage9
2024-03-22 00:07:00	0.6768534194102499	INV09_INVERTER_SG_Branch Voltage19
2024-03-22 00:09:00	0.2907072230437511	INV06_INVERTER_SG_Branch Voltage3
2024-03-22 00:09:00	1.3618524145407915	INV03_INVERTER_SG_Branch Voltage23
2024-03-22 00:13:00	0.03309596412063995	INV09_INVERTER_SG_Branch Voltage23
2024-03-22 00:14:00	0.24989200485469099	INV12_INVERTER_SG_Branch Voltage5
2024-03-22 00:14:00	0.5135056910779185	INV01_INVERTER_SG_Branch Voltage11
2024-03-22 00:16:00	0.13517425939443212	INV08_INVERTER_SG_Branch Voltage9
2024-03-22 00:20:00	2.657070883085778	INV18_INVERTER_SG_Branch Voltage13
2024-03-22 00:22:00	3.592830362401637	INV15_INVERTER_SG_Branch Voltage3
2024-03-22 00:26:00	3.382074211594285	INV15_INVERTER_SG_Branch Voltage13
2024-03-22 00:28:00	0.10588884732543108	INV16_INVERTER_SG_Branch Voltage3
2024-03-22 00:28:00	0.96926796908084	INV09_INVERTER_SG_Branch Voltage5
2024-03-22 00:28:00	0.09788700606805915	INV02_INVERTER_SG_Branch Voltage1