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 SEQUENCE ID_spender
  start 1
  increment 1;

create table kostenstelle (
  ks_id integer primary key default nextval('ID_Spender'), 
  ks_name varchar(50),
  ks_name_alternativ varchar(50));
  
create unique index  kostenstelle_name 
    on kostenstelle (ks_name);

alter table kostenstelle 
  add constraint ks_name unique
      using index kostenstelle_name;

create unique index  kostenstelle_name_alternativ 
    on kostenstelle (ks_name_alternativ);

alter table kostenstelle 
  add constraint kostenstelle_name_alternativ unique
      using index kostenstelle_name_alternativ;


create table kita (
  kt_id serial primary key, 
  kt_name varchar(50), 
  kt_ks_id integer, 
  kt_gruppen_groesse_max smallint,  
  constraint kita_kostenstelle
      foreign key(kt_ks_id) 
	  references kostenstelle(ks_id));
insert into kostenstelle values
(1, 'U3- Bereich', 'U3'),
(2, 'KiGa- Bereich', 'KiGa'),
(3, 'Hort- Bereich', 'Hort');

insert into kita (kt_name,kt_ks_id, kt_gruppen_groesse_max ) values 
( 'Sonnenblume', 3, 24),
( 'Punk', 1, 5),
( 'Rasselbande', 2, 16);
3 rows affected

3 rows affected

Select * from kostenstelle;
Select * from kita;
Select * from kita kt join kostenstelle ks
  on kt.kt_id = ks.ks_id;


ks_id
ks_name
ks_name_alternativ


1
U3- Bereich
U3

2
KiGa- Bereich
KiGa

3
Hort- Bereich
Hort




kt_id
kt_name
kt_ks_id
kt_gruppen_groesse_max


1
Sonnenblume
3
24

2
Punk
1
5

3
Rasselbande
2
16




kt_id
kt_name
kt_ks_id
kt_gruppen_groesse_max
ks_id
ks_name
ks_name_alternativ


1
Sonnenblume
3
24
1
U3- Bereich
U3

2
Punk
1
5
2
KiGa- Bereich
KiGa

3
Rasselbande
2
16
3
Hort- Bereich
Hort


Create View i_kita as select * from kita kt join kostenstelle ks
  on kt.kt_ks_id = ks.ks_id;
  
Select * from i_kita;
  -- funktioniert, aber macht nicht 100% Sinn


kt_id
kt_name
kt_ks_id
kt_gruppen_groesse_max
ks_id
ks_name
ks_name_alternativ


1
Sonnenblume
3
24
3
Hort- Bereich
Hort

2
Punk
1
5
1
U3- Bereich
U3

3
Rasselbande
2
16
2
KiGa- Bereich
KiGa


  drop view i_kita;
Create View i_kita as 
select kt.*, 
       ks.ks_name, ks.ks_name_alternativ 
  from kita kt join kostenstelle ks
    on kt.kt_ks_id = ks.ks_id;
 
Select * from i_kita;
-- besser


kt_id
kt_name
kt_ks_id
kt_gruppen_groesse_max
ks_name
ks_name_alternativ


1
Sonnenblume
3
24
Hort- Bereich
Hort

2
Punk
1
5
U3- Bereich
U3

3
Rasselbande
2
16
KiGa- Bereich
KiGa


insert into kita (kt_name,kt_ks_id, kt_gruppen_groesse_max ) 
values ('Mc Donald', 3, 25);
1 rows affected

select * from i_kita;


kt_id
kt_name
kt_ks_id
kt_gruppen_groesse_max
ks_name
ks_name_alternativ


1
Sonnenblume
3
24
Hort- Bereich
Hort

2
Punk
1
5
U3- Bereich
U3

3
Rasselbande
2
16
KiGa- Bereich
KiGa

4
Mc Donald
3
25
Hort- Bereich
Hort

kt_id	kt_name	kt_ks_id	kt_gruppen_groesse_max	ks_id	ks_name	ks_name_alternativ
1	Sonnenblume	3	24	1	U3- Bereich	U3
2	Punk	1	5	2	KiGa- Bereich	KiGa
3	Rasselbande	2	16	3	Hort- Bereich	Hort