Postgres 15

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 user_tbl(
   user_id int generated by default as identity primary key
  ,name text);
create table metrics_tbl(
   user_id int references user_tbl(user_id)
  ,total_jobs numeric
  ,jobs_applied numeric
  ,job_limit numeric);

create table applied_tbl(
  user_id int references user_tbl(user_id));
create table subscriptions_tbl(
   price_id text default 'id_AAAAAAAAAAAAAAAAAAAAAAAA'
  ,user_id int references user_tbl(user_id))

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE


insert into user_tbl(name)values('user1');
insert into metrics_tbl(user_id)values(1);
insert into applied_tbl(user_id)values(1),(1),(1);
insert into subscriptions_tbl(user_id)values(1);

INSERT 0 1



INSERT 0 1



INSERT 0 3



INSERT 0 1


alter table metrics_tbl
    drop column if exists job_limit
   ,add column job_limit numeric 
      generated always as(total_jobs-jobs_applied)stored;

ALTER TABLE


select * from metrics_tbl;


user_id
total_jobs
jobs_applied
job_limit


1
null
null
null



SELECT 1


UPDATE metrics_tbl
SET jobs_applied =(SELECT COUNT(user_id) 
                   FROM applied_tbl
                   WHERE applied_tbl.user_id = metrics_tbl.user_id)
   ,total_jobs =CASE price_id
                WHEN 'id_AAAAAAAAAAAAAAAAAAAAAAAA' THEN 100
                WHEN 'id_BBBBBBBBBBBBBBBBBBBBBBBB' THEN 150
                WHEN 'id_CCCCCCCCCCCCCCCCCCCCCCCC' THEN 200
                WHEN 'id_DDDDDDDDDDDDDDDDDDDDDDDD' THEN 250
                WHEN 'id_EEEEEEEEEEEEEEEEEEEEEEEE' THEN 300
                WHEN 'id_FFFFFFFFFFFFFFFFFFFFFFFF' THEN 350
                ELSE 0
                END
FROM subscriptions_tbl
WHERE metrics_tbl.user_id = subscriptions_tbl.user_id
RETURNING metrics_tbl.*; 


user_id
total_jobs
jobs_applied
job_limit


1
100
3
97



UPDATE 1