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 category (
id serial,
parent_id integer default null,
tree_depth smallint NOT NULL,
PRIMARY KEY (id),
UNIQUE (parent_id, id),
FOREIGN KEY (parent_id) REFERENCES category (id)
);
CREATE TABLE category_lang (
id serial,
category_id integer NOT NULL,
lang_code char(2) NOT NULL,
title varchar(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (category_id, lang_code)
-- foreign key (category_id) references category (id) -- missing?
);
CREATE OR REPLACE FUNCTION pg_temp.fn_category__bins ()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
-- calculate tree_depth as parent tree_depth + 1
if new.parent_id is null then
new.tree_depth = 0;
else
new.tree_depth = (select tree_depth from category where id = new.parent_id limit 1) + 1;
end if;
return new;
END
$$;
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 12
INSERT 0 12
INSERT 0 12
-- once per database
CREATE TYPE title_id AS (title varchar(128), id int);
CREATE TYPE
-- EN
WITH RECURSIVE tree AS (
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, ARRAY[(l.title, l.category_id)::title_id] AS sort_arr
FROM category c
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = 'EN'
WHERE c.parent_id IS NULL -- root cat
UNION ALL
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, sort_arr || (l.title, l.category_id)::title_id
FROM tree t
JOIN category c ON c.parent_id = t.cat_id
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = t.lang_code
)
SELECT cat_id, parent_id, tree_depth, lang_id, title
FROM tree
ORDER BY sort_arr;
| cat_id | parent_id | tree_depth | lang_id | title |
|---|---|---|---|---|
| 1 | null | 0 | 1 | One |
| 11 | 1 | 1 | 4 | One.One |
| 13 | 1 | 1 | 6 | One.Three |
| 12 | 1 | 1 | 5 | One.Two |
| 3 | null | 0 | 3 | Three |
| 31 | 3 | 1 | 9 | Three.One |
| 2 | null | 0 | 2 | Two |
| 21 | 2 | 1 | 7 | Two.One |
| 211 | 21 | 2 | 10 | Two.One.One |
| 213 | 21 | 2 | 12 | Two.One.Three |
| 212 | 21 | 2 | 11 | Two.One.Two |
| 22 | 2 | 1 | 8 | Two.Two |
SELECT 12
-- ES
WITH RECURSIVE tree AS (
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, ARRAY[(l.title, l.category_id)::title_id] AS sort_arr
FROM category c
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = 'ES' -- provide once
WHERE c.parent_id IS NULL -- root cat
UNION ALL
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, sort_arr || (l.title, l.category_id)::title_id
FROM tree t
JOIN category c ON c.parent_id = t.cat_id
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = t.lang_code
)
SELECT cat_id, parent_id, tree_depth, lang_id, title
FROM tree
ORDER BY sort_arr;
| cat_id | parent_id | tree_depth | lang_id | title |
|---|---|---|---|---|
| 2 | null | 0 | 14 | Dos |
| 22 | 2 | 1 | 20 | Dos.Dos |
| 21 | 2 | 1 | 19 | Dos.Uno |
| 212 | 21 | 2 | 23 | Dos.Uno.Dos |
| 213 | 21 | 2 | 24 | Dos.Uno.Tres |
| 211 | 21 | 2 | 22 | Dos.Uno.Uno |
| 3 | null | 0 | 15 | Tres |
| 31 | 3 | 1 | 21 | Tres.Uno |
| 1 | null | 0 | 13 | Uno |
| 12 | 1 | 1 | 17 | Uno.Dos |
| 13 | 1 | 1 | 18 | Uno.Tres |
| 11 | 1 | 1 | 16 | Uno.Uno |
SELECT 12