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?.
select version();
| version |
|---|
| PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
create table table_a ( id integer generated always as identity
primary key
, track_code text not null
, create_dtts timestamptz not null default current_timestamp
, a_val integer not null
);
create table table_b ( id integer generated always as identity
primary key
, track_code text not null
, create_dtts timestamptz not null default clock_timestamp()
, items integer not null
) ;
CREATE TABLE
CREATE TABLE
create or replace function group_a_ais()
returns trigger
language 'plpgsql'
as $$
begin
insert into table_b(track_code, items)
select track_code, count(*)
from rows_inserted_to_a
group by track_code ;
return null;
end;
$$;
create trigger table_a_ais
after insert on table_a
referencing new table as rows_inserted_to_a
for each statement
execute function group_a_ais();
CREATE FUNCTION
CREATE TRIGGER
select *
from table_a;
select *
from table_b
order by track_code;
| id | track_code | create_dtts | a_val |
|---|
SELECT 0
| id | track_code | create_dtts | items |
|---|
SELECT 0
insert into table_a (track_code, a_val)
select 'TC-' || ((100*random())::integer+1)%4
, (20*random())::integer+1
from generate_series(1,15);
INSERT 0 15
select *
from table_a;
select *
from table_b
order by track_code;
| id | track_code | create_dtts | a_val |
|---|---|---|---|
| 1 | TC-3 | 2024-10-01 13:32:13.865436+01 | 12 |
| 2 | TC-1 | 2024-10-01 13:32:13.865436+01 | 10 |
| 3 | TC-3 | 2024-10-01 13:32:13.865436+01 | 16 |
| 4 | TC-2 | 2024-10-01 13:32:13.865436+01 | 1 |
| 5 | TC-1 | 2024-10-01 13:32:13.865436+01 | 12 |
| 6 | TC-3 | 2024-10-01 13:32:13.865436+01 | 12 |
| 7 | TC-2 | 2024-10-01 13:32:13.865436+01 | 11 |
| 8 | TC-0 | 2024-10-01 13:32:13.865436+01 | 17 |
| 9 | TC-1 | 2024-10-01 13:32:13.865436+01 | 13 |
| 10 | TC-2 | 2024-10-01 13:32:13.865436+01 | 7 |
| 11 | TC-0 | 2024-10-01 13:32:13.865436+01 | 18 |
| 12 | TC-1 | 2024-10-01 13:32:13.865436+01 | 3 |
| 13 | TC-0 | 2024-10-01 13:32:13.865436+01 | 3 |
| 14 | TC-3 | 2024-10-01 13:32:13.865436+01 | 2 |
| 15 | TC-3 | 2024-10-01 13:32:13.865436+01 | 11 |
SELECT 15
| id | track_code | create_dtts | items |
|---|---|---|---|
| 2 | TC-0 | 2024-10-01 13:32:13.870302+01 | 3 |
| 1 | TC-1 | 2024-10-01 13:32:13.870091+01 | 4 |
| 4 | TC-2 | 2024-10-01 13:32:13.870317+01 | 3 |
| 3 | TC-3 | 2024-10-01 13:32:13.870312+01 | 5 |
SELECT 4