Postgres 9.4

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 products
    (id SERIAL, title varchar(50), image varchar(50), PRIMARY KEY(id))

CREATE TABLE


INSERT INTO products
    (title,image)
VALUES
    ('Photo', 'photo.jpg'),    
    ('Book', 'book.jpg'),
    ('Table', 'table.jpg')

INSERT 0 3


CREATE TABLE users
    (id SERIAL, username varchar(50), PRIMARY KEY(id))

CREATE TABLE


INSERT INTO users
    (username)
VALUES
    ('Admin'),    
    ('John'),
    ('Jane')

INSERT 0 3


CREATE TABLE product_review
    (id SERIAL, product_id int NOT NULL, user_id int NOT NULL, review varchar(50), PRIMARY KEY(id), FOREIGN KEY (product_id) references products, FOREIGN KEY (user_id) references users)

CREATE TABLE


INSERT INTO product_review
    (product_id, user_id, review)
VALUES
    (1, 1, 'Great Product!'),
    (1, 2, 'Looks Great'),
    (2, 1, 'Could be better')

INSERT 0 3


CREATE TABLE product_like
    (id SERIAL, product_id int NOT NULL, user_id int NOT NULL, PRIMARY KEY(id), FOREIGN KEY (product_id) references products, FOREIGN KEY (user_id) references users)

CREATE TABLE


INSERT INTO product_like
    (product_id, user_id)
VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (2, 3)

INSERT 0 4


SELECT "products".*, 
array_to_json(array_agg("product_review".*)) as reviews,
EXISTS(SELECT * FROM product_like lk
JOIN users u ON u.id = "lk"."user_id" WHERE u.id = 4
AND "lk"."product_id" = products.id) AS liked,
COUNT("product_like"."product_id") AS totalLikes from "products"
LEFT JOIN "product_review" on "product_review"."product_id" = "products"."id" 
LEFT JOIN "product_like" on "product_like"."product_id" = "products"."id" 
group by "products"."id"


id
title
image
reviews
liked
totallikes


2
Book
book.jpg
[{"id":3,"product_id":2,"user_id":1,"review":"Could be better"},{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]
f
2

1
Photo
photo.jpg
[{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"},{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"},{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"}]
f
4

3
Table
table.jpg
[null]
f
0



SELECT 3


SELECT "products".*, 
"pr"."reviews",
EXISTS(SELECT * FROM product_like lk
JOIN users u ON u.id = "lk"."user_id" WHERE u.id = 4
AND "lk"."product_id" = products.id) AS liked,
COALESCE("pl"."totalLikes", 0) AS totalLikes
FROM "products"
LEFT JOIN (SELECT product_id, array_to_json(array_agg("product_review".*)) AS reviews
           FROM "product_review"
           GROUP BY product_id) "pr" on "pr"."product_id" = "products"."id" 
LEFT JOIN (SELECT product_id, COUNT(*) AS "totalLikes"
           FROM "product_like"
           GROUP BY product_id) "pl" on "pl"."product_id" = "products"."id" 


id
title
image
reviews
liked
totallikes


1
Photo
photo.jpg
[{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"}]
f
2

2
Book
book.jpg
[{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]
f
2

3
Table
table.jpg
null
f
0



SELECT 3

id	title	image	reviews	liked	totallikes
2	Book	book.jpg	[{"id":3,"product_id":2,"user_id":1,"review":"Could be better"},{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]	f	2
1	Photo	photo.jpg	[{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"},{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"},{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"}]	f	4
3	Table	table.jpg	[null]	f	0

id	title	image	reviews	liked	totallikes
1	Photo	photo.jpg	[{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"}]	f	2
2	Book	book.jpg	[{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]	f	2
3	Table	table.jpg	null	f	0