Postgres 10

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 "public"."posts" (
    "id" bigint NOT NULL,
    "text" "text",
    "user_id" "uuid" NOT NULL
);

INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (173, 'iOS created', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (174, 'chrome reply', '90a42a87-9f9b-4676-bc6a-214be0050053');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (175, 'brave reply', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (176, '@1 chrome mention', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (177, '@0 ios mention', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (178, '', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (179, '@2 mention brave', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from
(
  select 
  --DISTINCT ON (user_id)
  text,
  row_number() over() rank,
  user_id
  from
  (
    SELECT 
    id,
    text,
    user_id
    FROM posts
    order by id asc
  ) alias1
) alias2


text
rank
user_id


iOS created
1
9361a62b-462a-4ca3-ba1d-e0c2e0174aa0

chrome reply
2
90a42a87-9f9b-4676-bc6a-214be0050053

brave reply
3
00bf9dae-e5a7-4f86-a3fd-0be4630cffd4

@1 chrome mention
4
9361a62b-462a-4ca3-ba1d-e0c2e0174aa0

@0 ios mention
5
00bf9dae-e5a7-4f86-a3fd-0be4630cffd4


6
00bf9dae-e5a7-4f86-a3fd-0be4630cffd4

@2 mention brave
7
9361a62b-462a-4ca3-ba1d-e0c2e0174aa0


SELECT rank,
       user_id,
       text
       FROM (SELECT DISTINCT ON (user_id)
                    row_number() OVER (ORDER BY id) AS rank,
                    user_id,
                    text
                    FROM posts
                    ORDER BY user_id,
                             id) AS x
       ORDER BY rank;


rank
user_id
text


1
9361a62b-462a-4ca3-ba1d-e0c2e0174aa0
iOS created

2
90a42a87-9f9b-4676-bc6a-214be0050053
chrome reply

3
00bf9dae-e5a7-4f86-a3fd-0be4630cffd4
brave reply

text	rank	user_id
iOS created	1	9361a62b-462a-4ca3-ba1d-e0c2e0174aa0
chrome reply	2	90a42a87-9f9b-4676-bc6a-214be0050053
brave reply	3	00bf9dae-e5a7-4f86-a3fd-0be4630cffd4
@1 chrome mention	4	9361a62b-462a-4ca3-ba1d-e0c2e0174aa0
@0 ios mention	5	00bf9dae-e5a7-4f86-a3fd-0be4630cffd4
	6	00bf9dae-e5a7-4f86-a3fd-0be4630cffd4
@2 mention brave	7	9361a62b-462a-4ca3-ba1d-e0c2e0174aa0