Postgres 12

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 "user" ( 
	"id" Character Varying( 27 ) NOT NULL,
	"name" Character Varying( 27 ) NOT NULL)
CREATE TABLE "other_table" ( 
	"id" Character Varying( 27 ) NOT NULL,
    "user_id" Character Varying( 27 ) NOT NULL,
	"address" Character Varying( 27 ) NOT NULL);
INSERT INTO "user" ("id", "name") VALUES ('1000', 'joe');
1 rows affected

INSERT INTO "other_table" ("id", "user_id", "address") VALUES ('1', '1000', 'elm street 4');  
1 rows affected

SELECT * FROM "user"


id
name


1000
joe


SELECT * FROM "other_table"


id
user_id
address


1
1000
elm street 4


-- WITH JOIN ALIAS
SELECT "user".id, "user".name, "external".address
FROM "user"
INNER JOIN ( SELECT * FROM other_table ) AS external ON "user".id = "external".user_id;


id
name
address


1000
joe
elm street 4


-- WITHOUT JOIN ALIAS
SELECT "user".id, "user".name, "external".address
FROM "user"
INNER JOIN ( SELECT * FROM other_table ) ON "user".id = "external".user_id;
ERROR:  subquery in FROM must have an alias
LINE 4: INNER JOIN ( SELECT * FROM other_table ) ON "user".id = "ext...
                   ^
HINT:  For example, FROM (SELECT ...) [AS] foo.