Postgres 16

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 product (
  id INT NOT NULL PRIMARY KEY,
  template_id INT NOT NULL,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE template (
  id INT NOT NULL PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE template_property (
  id INT NOT NULL PRIMARY KEY,
  template_id INT NOT NULL,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE product_property_value (
  id INT NOT NULL PRIMARY KEY,
  product_id INT NOT NULL,
  property_id INT NOT NULL,
  value TEXT
);

CREATE TABLE


INSERT INTO template(id, title)
VALUES 
  (1, 'Телевизор'), (2, 'Смартфон'), (3, 'Наушники');

INSERT INTO product(id, template_id, title)
VALUES 
  (1, 1, 'Xiaomi MI TV A2 43'), 
  (2, 1, 'Samsung UE65CU7100UXRU'), 
  (3, 2, 'Apple iPhone 13'),
  (4, 2, 'Apple iPhone 14'),
  (5, 2, 'Apple iPhone 15');

INSERT INTO template_property(id, template_id, title)
VALUES 
  (1, 1, 'Диагональ'), 
  (2, 1, 'Разрешение'), 
  (3, 2, 'Цвет'),
  (4, 2, 'Ширина'),
  (5, 2, 'Количество разъемов');

INSERT INTO product_property_value(id, product_id, property_id, value)
VALUES 
  (1, 1, 1, '43"'), (2, 1, 2, '4K UltraHD, 3840x2160'), 
  (3, 3, 3, 'черный'), (4, 3, 4, '71.6мм'), (5, 3, 5, '1'),
  (6, 4, 3, 'черный'), (7, 4, 4, '71.6мм'), (8, 4, 5, '2'),
  (9, 5, 3, 'черный'), (10, 5, 4, '71.6мм'), (11, 5, 5, '3');

INSERT 0 3



INSERT 0 5



INSERT 0 5



INSERT 0 11


SELECT DISTINCT p.id, p.title
FROM product_property_value AS ppv
INNER JOIN product AS p
ON p.id = ppv.product_id
WHERE ppv.property_id = 3 AND value = 'черный'


id
title


3
Apple iPhone 13

4
Apple iPhone 14

5
Apple iPhone 15



SELECT 3


SELECT p.id, p.title, tp.title, ppv.value
FROM product_property_value AS ppv
INNER JOIN product AS p
ON p.id = ppv.product_id
INNER JOIN template_property AS tp
ON tp.id = ppv.property_id
WHERE ppv.property_id = 5


id
title
title
value


3
Apple iPhone 13
Количество разъемов
1

4
Apple iPhone 14
Количество разъемов
2

5
Apple iPhone 15
Количество разъемов
3



SELECT 3


WITH choosen_properties AS (
  SELECT DISTINCT id, filter.value, filter.type FROM template_property AS tp
  INNER JOIN (
      VALUES 
        ('Цвет', 'черный', 'equals'), 
        ('Ширина', '71.6мм', 'equals'), 
        ('Количество разъемов', '1-2', 'range')
  ) AS filter(property, value, type)
  ON tp.title = filter.property
), 
filtered_products AS (
  SELECT DISTINCT 
    ppv.product_id,
    COUNT(ppv.product_id) OVER(PARTITION BY ppv.product_id) AS passed_filters
  FROM product_property_value AS ppv
  INNER JOIN choosen_properties
  ON choosen_properties.id = ppv.property_id
  AND 
    CASE 
      WHEN choosen_properties.type = 'range' THEN
        ppv.value BETWEEN 
          split_part(choosen_properties.value, '-', 1) 
          AND 
          split_part(choosen_properties.value, '-', 2)
      WHEN choosen_properties.type = 'equals' THEN
        choosen_properties.value = ppv.value
    END
)

SELECT p.id, p.title
FROM filtered_products
INNER JOIN product AS p
ON p.id = filtered_products.product_id
WHERE filtered_products.passed_filters = (SELECT COUNT(*) FROM choosen_properties)



id
title


3
Apple iPhone 13

4
Apple iPhone 14



SELECT 2

id	title	title	value
3	Apple iPhone 13	Количество разъемов	1
4	Apple iPhone 14	Количество разъемов	2
5	Apple iPhone 15	Количество разъемов	3