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 poi (id SERIAL NOT NULL, id_url VARCHAR(255) DEFAULT NULL, titre TEXT DEFAULT NULL, description_courte TEXT DEFAULT NULL, description_longue TEXT DEFAULT NULL, latitude DOUBLE PRECISION DEFAULT NULL, longitude DOUBLE PRECISION DEFAULT NULL, is_personne_mobilite_reduite BOOLEAN DEFAULT NULL, last_update TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, last_update_datatourisme TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE TABLE equipement (id SERIAL NOT NULL, subject VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, last_check_boot TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_B8B4C6F3FBCE3E7A ON equipement (subject);
CREATE TABLE poi_equipement (poi_id INT NOT NULL, equipement_id INT NOT NULL, PRIMARY KEY(poi_id, equipement_id));
CREATE INDEX IDX_17D414177EACE855 ON poi_equipement (poi_id);
ALTER TABLE poi_equipement ADD CONSTRAINT FK_17D41417806F0F5C FOREIGN KEY (equipement_id) REFERENCES equipement (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

insert into poi (id, id_url,titre) values (1, 'url1', 'test');
insert into equipement (id, subject) values (1,'wifi');
insert into equipement (id, subject) values (2,'kitchen');
insert into equipement (id, subject) values (3,'wc');

create table data (id serial not null, subject text, predicate text, object text, lang varchar(20));
insert into data (subject, object) values ('url1', 'wifi');
insert into data (subject, object) values ('url1', 'kitchen');
insert into data (subject, object) values ('url1', 'hotel');

insert into poi_equipement (poi_id, equipement_id) values (1, 1);
insert into poi_equipement (poi_id, equipement_id) values (1, 2);
insert into poi_equipement (poi_id, equipement_id) values (1, 3);

select * from poi_equipement;
select object from data where subject = 'url1';

select subject from poi_equipement inner join equipement on poi_equipement.equipement_id = equipement.id;

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

ALTER TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

poi_id	equipement_id
1	1
1	2
1	3

SELECT 3

object
wifi
kitchen
hotel

SELECT 3

subject
wifi
kitchen
wc

SELECT 3