Postgres 9.5

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

personid	lastname	firstname	address	city
8	Smith3	Eric3	718 Louise Circle	Madrid
4	Smith	Eric	713 Louise Circle	Paris