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 line (geom geometry(linestring,4326));
INSERT INTO line VALUES ('SRID=4326;LINESTRING(-4.70 54.09,-4.71 54.12,-4.68 54.13,-4.67 54.14,-4.63 54.18,-4.53 54.21,-4.59 54.24 )'::geometry);

CREATE TABLE points AS
SELECT path,geom
FROM ST_DumpPoints('SRID=4326;LINESTRING(-4.70 54.09,-4.71 54.12,-4.68 54.13,-4.67 54.14,-4.63 54.18,-4.53 54.21,-4.59 54.24)'::geometry) j(path,geom)

CREATE TABLE

INSERT 0 1

SELECT 7

SELECT ST_AsText(geom) 
FROM points 
ORDER BY path;

st_astext

POINT(-4.7 54.09)

POINT(-4.71 54.12)

POINT(-4.68 54.13)

POINT(-4.67 54.14)

POINT(-4.63 54.18)

POINT(-4.53 54.21)

POINT(-4.59 54.24)

SELECT 7

SELECT 
  ST_AsText(points.geom)
FROM line
JOIN points ON 
  ST_Intersects(points.geom,
    ST_LineSubstring(
     line.geom,
     ST_LineLocatePoint(line.geom,'SRID=4326;POINT(-4.71 54.12)'::geometry),
     ST_LineLocatePoint(line.geom,'SRID=4326;POINT(-4.53 54.21)'::geometry)))
ORDER BY path;

st_astext

POINT(-4.71 54.12)

POINT(-4.68 54.13)

POINT(-4.67 54.14)

POINT(-4.63 54.18)

POINT(-4.53 54.21)

SELECT 5