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?.

CREATE TYPE ownership_state AS ENUM ('wished', 'owned');

CREATE TABLE "users" (
    "id" serial PRIMARY KEY,
    "username" varchar(50)
);

CREATE TABLE "products" (
    "id" serial PRIMARY KEY,
    "name" varchar(50),
    "owned_count" integer DEFAULT 0,
    "wished_count" integer DEFAULT 0
);

CREATE TABLE "users_products" (
    "user_id" integer REFERENCES "users" ("id"),
    "product_id" integer REFERENCES "products" ("id"),
    "ownership" ownership_state,
    PRIMARY KEY ("user_id", "product_id")
);

CREATE OR REPLACE FUNCTION update_products_ownership_count ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        IF (OLD.ownership IS NULL AND NEW.ownership = 'wished') THEN
            UPDATE products SET wished_count = wished_count + 1 WHERE products.id = NEW.product_id;
        ELSEIF (OLD.ownership IS NULL AND NEW.ownership = 'owned') THEN
            UPDATE products SET owned_count = owned_count + 1 WHERE products.id = NEW.product_id;
        ELSEIF (OLD.ownership = 'wished' AND NEW.ownership = 'owned') THEN
            UPDATE products SET wished_count = wished_count - 1, owned_count = owned_count + 1 WHERE products.id = NEW.product_id;
        ELSEIF (OLD.ownership = 'wished' AND NEW.ownership IS NULL) THEN
            UPDATE products SET wished_count = wished_count - 1 WHERE products.id = NEW.product_id;
        ELSEIF (OLD.ownership = 'owned' AND NEW.ownership = 'wished') THEN
            UPDATE products SET wished_count = wished_count + 1, owned_count = owned_count - 1 WHERE products.id = NEW.product_id;
        ELSEIF (OLD.ownership = 'owned' AND NEW.ownership IS NULL) THEN
            UPDATE products SET owned_count = owned_count - 1 WHERE products.id = NEW.product_id;
        END IF;
    ELSEIF (TG_OP = 'INSERT') THEN
        IF (NEW.ownership = 'wished') THEN
            UPDATE products SET wished_count = wished_count + 1 WHERE products.id = NEW.product_id;
        ELSEIF (NEW.ownership = 'owned') THEN
            UPDATE products SET owned_count = owned_count + 1 WHERE products.id = NEW.product_id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER update_ownership_count
    AFTER UPDATE OF ownership OR INSERT ON "users_products"
    FOR EACH ROW
    EXECUTE PROCEDURE update_products_ownership_count ();

INSERT INTO
    "users" ("username")
VALUES
    ('user1'),
    ('user2'),
    ('user3'),
    ('user4'),
    ('user5');

INSERT INTO
    "products" ("name")
VALUES
    ('product1'),
    ('product2'),
    ('product3'),
    ('product4'),
    ('product5');

INSERT INTO
    "users_products" ("user_id", "product_id", "ownership")
VALUES
    ('1', '1', 'owned'),
    ('1', '2', 'owned'),
    ('1', '3', 'owned'),
    ('1', '4', 'owned'),
    ('1', '5', 'owned'),
    ('2', '1', 'wished'),
    ('2', '2', 'wished'),
    ('2', '3', 'wished'),
    ('2', '4', 'wished'),
    ('2', '5', 'wished'),
    ('3', '1', NULL),
    ('3', '2', NULL),
    ('3', '3', NULL),
    ('3', '4', NULL),
    ('3', '5', NULL),
    ('4', '1', 'owned'),
    ('4', '2', 'wished'),
    ('4', '3', 'owned'),
    ('4', '4', 'wished'),
    ('4', '5', 'owned'),
    ('5', '1', 'owned'),
    ('5', '2', 'wished'),
    ('5', '3', 'owned'),
    ('5', '4', 'wished'),
    ('5', '5', 'owned');

SELECT * FROM products;

5 rows affected

5 rows affected

25 rows affected

id	name	owned_count	wished_count
1	product1	3	1
2	product2	1	3
3	product3	3	1
4	product4	1	3
5	product5	3	1