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 Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
insert into persons values (4,'Smith','Eric','713 Louise Circle','Paris');
insert into persons values (5,'Smith2','Eric2','715 Louise Circle','London');
insert into persons values (8,'Smith3','Eric3','718 Louise Circle','Madrid');
1 rows affected
1 rows affected
1 rows affected
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
WHERE pn.address LIKE concat('%', pn.n, '%')
AND pn.n IN (1, 2, 3);
| personid | lastname | firstname | address | city |
|---|---|---|---|---|
| 8 | Smith3 | Eric3 | 718 Louise Circle | Madrid |
| 4 | Smith | Eric | 713 Louise Circle | Paris |
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
WHERE pn.address LIKE concat('%', pn.n, '%')
AND pn.n IN (SELECT s.n
FROM generate_series(1, 3) s (n));
| personid | lastname | firstname | address | city |
|---|---|---|---|---|
| 8 | Smith3 | Eric3 | 718 Louise Circle | Madrid |
| 4 | Smith | Eric | 713 Louise Circle | Paris |
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
INNER JOIN generate_series(1, 3) s (n)
ON s.n = pn.n
WHERE pn.address LIKE concat('%', pn.n, '%');
| personid | lastname | firstname | address | city |
|---|---|---|---|---|
| 8 | Smith3 | Eric3 | 718 Louise Circle | Madrid |
| 4 | Smith | Eric | 713 Louise Circle | Paris |