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 table_1 (
    "signalID" text not null,
    "timestamp" timestamp not null,
    "value" numeric not null );

CREATE TABLE


create table table_all_signals (
    source_id int not null,
    signal_id text not null,
    signal_timestamp timestamp not null,
    value numeric not null );

begin;
create rule table_1_singal_id_i as on insert to table_1
  do instead 
  insert into table_all_signals select 1::int,new.* 
  returning signal_id as "signalID", 
            signal_timestamp as "timestamp",
            value as "value";

create rule table_1_singal_id_u as on update to table_1
  do instead 
  update table_all_signals
  set signal_id=new."signalID", 
      signal_timestamp=new."timestamp",
      value=new."value"
  where source_id=1
  and signal_id=old."signalID"
  and signal_timestamp=old."timestamp"
  and value=old."value"
  returning signal_id as "signalID", 
            signal_timestamp as "timestamp",
            value as "value";

create rule table_1_singal_id_d as on delete to table_1
  do instead 
  delete from table_all_signals
  where source_id=1
  and signal_id=old."signalID"
  and signal_timestamp=old."timestamp"
  and value=old."value"
  returning signal_id as "signalID", 
            signal_timestamp as "timestamp",
            value as "value";
commit;

insert into table_all_signals select 1,* from table_1;

CREATE TABLE



BEGIN



CREATE RULE



CREATE RULE



CREATE RULE



COMMIT



INSERT 0 0


--instead of inserting into `table_1` 
--this will now get redirected to `table_all_signals`
insert into table_1 
  select gen_random_uuid(),
         '2023-11-21 12:46:49.734534'::timestamp-'100 microseconds'::interval*random(),
         round((random()*1e3)::numeric,6) 
  returning *;


signalID
timestamp
value


2a8a9cc5-23cb-423f-9a06-215bc90d3e16
2023-11-21 12:46:49.734502
123.257321



INSERT 0 1


--the row didn't go to `table_1`
select * from table_1;


signalID
timestamp
value



SELECT 0


--the row went to `table_all_signals` instead
select * from table_all_signals;


source_id
signal_id
signal_timestamp
value


1
2a8a9cc5-23cb-423f-9a06-215bc90d3e16
2023-11-21 12:46:49.734502
123.257321



SELECT 1


select setseed(0.2);
insert into table_all_signals 
select random()*5,
       gen_random_uuid(),
       '2023-11-21 12:46:49.734534'::timestamp-'100 microseconds'::interval*random(),
       round((random()*1e3)::numeric,6)
from generate_series(1,1e3,1);

select * from table_all_signals order by signal_timestamp,source_id limit 6;


setseed






SELECT 1



INSERT 0 1000




source_id
signal_id
signal_timestamp
value


0
90eb7637-e2ed-4780-996d-433accc5ae88
2023-11-21 12:46:49.734434
796.485640

1
27c2253d-4b74-4169-abc4-2cc905399c27
2023-11-21 12:46:49.734434
621.605431

1
1aff81cb-94f9-40eb-aaca-6e75e6742c70
2023-11-21 12:46:49.734434
711.898776

1
bea19573-0c00-4282-86c7-d21483defd9d
2023-11-21 12:46:49.734434
350.894326

2
d8c02e8d-570c-4f14-b38e-b01bcf08e16b
2023-11-21 12:46:49.734434
921.574251

3
70b9ec18-0ba5-4b85-8ad0-277c4f0da43d
2023-11-21 12:46:49.734434
484.759824



SELECT 6


CREATE COLLATION num_ignore_punct (provider = icu, 
   deterministic = false, 
  locale = 'und-u-ka-shifted-kn');

select '"source_'||
       string_agg(distinct source_id::text collate num_ignore_punct,
                  '_value" numeric, "source_' 
                  order by source_id::text collate num_ignore_punct)||
       '_value" numeric' AS column_list
from table_all_signals;

CREATE COLLATION




column_list


"source_0_value" numeric, "source_1_value" numeric, "source_2_value" numeric, "source_3_value" numeric, "source_4_value" numeric, "source_5_value" numeric



SELECT 1


create extension tablefunc;

CREATE EXTENSION


select * from crosstab($s1$ 
  select a.signal_timestamp, 
         b.source_id, 
         round(avg(value),6)
  from (select distinct signal_timestamp from table_all_signals) AS a 
  join (select distinct source_id from table_all_signals) AS b on true
  natural left join table_all_signals
  group by 1,2 order by 1,2
$s1$) AS ct(signal_timestamp timestamp, "source_0_value" numeric, "source_1_value" numeric, "source_2_value" numeric, "source_3_value" numeric, "source_4_value" numeric, "source_5_value" numeric)
limit 7;


signal_timestamp
source_0_value
source_1_value
source_2_value
source_3_value
source_4_value
source_5_value


2023-11-21 12:46:49.734434
796.485640
561.466178
921.574251
708.397401
null
null

2023-11-21 12:46:49.734435
604.862822
305.149533
727.721930
428.294901
750.385100
null

2023-11-21 12:46:49.734436
127.251620
920.933079
828.340270
466.197300
186.335424
324.038718

2023-11-21 12:46:49.734437
499.607598
362.309262
null
null
990.369557
68.402196

2023-11-21 12:46:49.734438
null
380.443741
262.119884
406.220658
371.185750
null

2023-11-21 12:46:49.734439
954.319612
297.565984
538.232769
342.743977
558.332414
421.902292

2023-11-21 12:46:49.73444
null
269.889969
248.913962
447.041819
182.455097
112.488082



SELECT 7


select * from crosstab($s1$ 
  select date_bin('10 microseconds'::interval,a.signal_timestamp,'today')::timestamp, 
         b.source_id, 
         round(avg(value),6)
  from (select distinct signal_timestamp from table_all_signals) AS a 
  join (select distinct source_id from table_all_signals) AS b on true
  natural left join table_all_signals
  where   a.signal_timestamp>'2023-11-21 12:46:49.734534'::timestamp
                           -'20 microseconds'::interval
  group by 1,2 order by 1,2
$s1$) AS ct(signal_timestamp timestamp, "source_0_value" numeric, "source_1_value" numeric, "source_2_value" numeric, "source_3_value" numeric, "source_4_value" numeric, "source_5_value" numeric)
limit 6;


signal_timestamp
source_0_value
source_1_value
source_2_value
source_3_value
source_4_value
source_5_value


2023-11-21 12:46:49.73451
599.218767
398.128466
603.169555
407.286343
538.080740
470.267812

2023-11-21 12:46:49.73452
461.830158
462.763604
521.590915
460.745277
420.974554
478.815830

2023-11-21 12:46:49.73453
199.177991
476.078663
601.812450
615.666513
514.503488
891.299843



SELECT 3

source_id	signal_id	signal_timestamp	value
0	90eb7637-e2ed-4780-996d-433accc5ae88	2023-11-21 12:46:49.734434	796.485640
1	27c2253d-4b74-4169-abc4-2cc905399c27	2023-11-21 12:46:49.734434	621.605431
1	1aff81cb-94f9-40eb-aaca-6e75e6742c70	2023-11-21 12:46:49.734434	711.898776
1	bea19573-0c00-4282-86c7-d21483defd9d	2023-11-21 12:46:49.734434	350.894326
2	d8c02e8d-570c-4f14-b38e-b01bcf08e16b	2023-11-21 12:46:49.734434	921.574251
3	70b9ec18-0ba5-4b85-8ad0-277c4f0da43d	2023-11-21 12:46:49.734434	484.759824

signal_timestamp	source_0_value	source_1_value	source_2_value	source_3_value	source_4_value	source_5_value
2023-11-21 12:46:49.734434	796.485640	561.466178	921.574251	708.397401	null	null
2023-11-21 12:46:49.734435	604.862822	305.149533	727.721930	428.294901	750.385100	null
2023-11-21 12:46:49.734436	127.251620	920.933079	828.340270	466.197300	186.335424	324.038718
2023-11-21 12:46:49.734437	499.607598	362.309262	null	null	990.369557	68.402196
2023-11-21 12:46:49.734438	null	380.443741	262.119884	406.220658	371.185750	null
2023-11-21 12:46:49.734439	954.319612	297.565984	538.232769	342.743977	558.332414	421.902292
2023-11-21 12:46:49.73444	null	269.889969	248.913962	447.041819	182.455097	112.488082

signal_timestamp	source_0_value	source_1_value	source_2_value	source_3_value	source_4_value	source_5_value
2023-11-21 12:46:49.73451	599.218767	398.128466	603.169555	407.286343	538.080740	470.267812
2023-11-21 12:46:49.73452	461.830158	462.763604	521.590915	460.745277	420.974554	478.815830
2023-11-21 12:46:49.73453	199.177991	476.078663	601.812450	615.666513	514.503488	891.299843