Postgres 14

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.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create type order_status_t as enum (
	'pending',
	'in progress',
	'complete');
create table orders 
       ( order_id    integer generated always as identity primary key 
       , cust_id     integer not null  -- references customers(cust_id) 
       );
      
insert into orders( cust_id )
     select 1+(random()*50)::integer
       from generate_series(1,7);
      
select * from orders;
7 rows affected



order_id
cust_id


1
4

2
42

3
33

4
30

5
15

6
33

7
28


create table order_products  
       ( ord_prod_id integer   generated always as identity primary key
	   , order_id    integer   not null  references orders(order_id) 
	   , prod_id     integer   not null
	   , status      order_status_t not null default 'pending'
       );
      
insert into order_products (order_id, prod_id, status) 
  values (1,20,'complete') 
       , (1,50,'in progress')
       , (1,75,'pending')     
       , (2,20,'in progress') 
       , (2,57,'in progress')
       , (2,48,'pending') 
       , (3,25,'complete') 
       , (3,18,'in progress')
       , (3,6, 'complete')   
       , (4,22,'complete') 
       , (4,18,'complete')
       , (4,64,'complete')       
       , (5,20,'complete') 
       , (6,82,'in progress')
       , (7,13,'complete'); 
      
select * from order_products;
15 rows affected



ord_prod_id
order_id
prod_id
status


1
1
20
complete

2
1
50
in progress

3
1
75
pending

4
2
20
in progress

5
2
57
in progress

6
2
48
pending

7
3
25
complete

8
3
18
in progress

9
3
6
complete

10
4
22
complete

11
4
18
complete

12
4
64
complete

13
5
20
complete

14
6
82
in progress

15
7
13
complete


create view order_status as  
       ( select order_id 
              , min(status) status  
           from order_products
          group by order_id
       );  
select * 
  from order_status 
  order by order_id;


order_id
status


1
pending

2
pending

3
in progress

4
complete

5
complete

6
in progress

7
complete


update order_products 
   set status = 'complete' 
 where order_id = 3 
   and prod_id  = 18;
1 rows affected

 select * 
   from order_status 
  order by order_id; 


order_id
status


1
pending

2
pending

3
complete

4
complete

5
complete

6
in progress

7
complete


-- order with its status
 select o.order_id
      , o.cust_id
      , os.status
   from orders       o 
   join order_status os 
     on (os.order_id = o.order_id);


order_id
cust_id
status


1
4
pending

2
42
pending

3
33
complete

4
30
complete

5
15
complete

6
33
in progress

7
28
complete

ord_prod_id	order_id	prod_id	status
1	1	20	complete
2	1	50	in progress
3	1	75	pending
4	2	20	in progress
5	2	57	in progress
6	2	48	pending
7	3	25	complete
8	3	18	in progress
9	3	6	complete
10	4	22	complete
11	4	18	complete
12	4	64	complete
13	5	20	complete
14	6	82	in progress
15	7	13	complete