Postgres 11

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 Comments
    ("id" int, "comment_id_no" int, "text" varchar(3), "show" varchar(5), "inserted_at" timestamp)
;
    
INSERT INTO Comments
    ("id", "comment_id_no", "text", "show", "inserted_at")
VALUES
    (1, 1, 'hi', 'true', '2000-01-01 00:00:00'),
    (2, 1, 'hey', 'true', '2001-01-01 00:00:00'),
    (3, 1, 'hey', 'false', '2002-01-01 00:00:00'),
    (4, 2, 'new', 'true', '2003-01-01 00:00:00')
;


CREATE TABLE



INSERT 0 4


SELECT	c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM	Comments AS c
		LEFT JOIN Comments AS c2
			ON c2.comment_id_no = c.comment_id_no
			AND c2.inserted_at > c.inserted_at
WHERE	c2.id IS NULL
AND		c.show = 'true';


id
comment_id_no
text
show
inserted_at


4
2
new
true
2003-01-01 00:00:00



SELECT 1


SELECT	*
FROM	(	SELECT	DISTINCT ON (c.comment_id_no) c.id, c.comment_id_no, c.text, c.show, c.inserted_at
		FROM	Comments AS c 
		ORDER By c.comment_id_no, inserted_at DESC
	) x
WHERE	show = 'true';


id
comment_id_no
text
show
inserted_at


4
2
new
true
2003-01-01 00:00:00



SELECT 1


SELECT	c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM	(	SELECT	c.id, 
					c.comment_id_no, 
					c.text, 
					c.show, 
					c.inserted_at,
					ROW_NUMBER() OVER(PARTITION BY c.comment_id_no ORDER BY c.inserted_at DESC) AS RowNumber
	        FROM	Comments AS c
		) AS c
WHERE	c.RowNumber = 1
AND		c.show = 'true';



id
comment_id_no
text
show
inserted_at


4
2
new
true
2003-01-01 00:00:00



SELECT 1