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 t (id int NOT NULL, geom geometry(LineStringZM,4326), CONSTRAINT t_pkey PRIMARY KEY (id));
INSERT INTO t VALUES (generate_series(1,10000),'SRID=4326;LINESTRING ZM(30 10 5 1618570148, 30 15 10 1618573748, 30 20 15 1618534148, 30 25 20 1618580948)'::geometry);

CREATE INDEX idx_t_geom_m2 ON t USING gist (geom) ;

CREATE TABLE

INSERT 0 10000

CREATE INDEX

EXPLAIN ANALYSE 
WITH j AS (
  SELECT id,geom,(ST_DumpPoints(geom)).geom AS p 
  FROM t 
) 
SELECT id,ST_AsText(ST_MakeLine(p))
FROM j
WHERE ST_M(p) BETWEEN 1618566548 AND 1618584548
GROUP BY id;

QUERY PLAN

GroupAggregate  (cost=250604316.41..250611318.41 rows=200 width=36) (actual time=75.414..156.190 rows=10000 loops=1)

  Group Key: j.id

  ->  Sort  (cost=250604316.41..250604441.41 rows=50000 width=36) (actual time=75.377..81.462 rows=30000 loops=1)

        Sort Key: j.id

        Sort Method: external merge  Disk: 1744kB

        ->  Subquery Scan on j  (cost=0.00..250600414.00 rows=50000 width=36) (actual time=0.036..61.264 rows=30000 loops=1)

              Filter: ((st_m(j.p) >= '1618566548'::double precision) AND (st_m(j.p) <= '1618584548'::double precision))

              Rows Removed by Filter: 10000

              ->  Result  (cost=0.00..250400414.00 rows=10000000 width=68) (actual time=0.033..49.032 rows=40000 loops=1)

                    ->  ProjectSet  (cost=0.00..300414.00 rows=10000000 width=36) (actual time=0.030..41.718 rows=40000 loops=1)

                          ->  Seq Scan on t  (cost=0.00..364.00 rows=10000 width=36) (actual time=0.010..2.746 rows=10000 loops=1)

Planning Time: 0.368 ms

Execution Time: 157.624 ms

EXPLAIN

ALTER TABLE t ADD COLUMN line_interval tstzrange;
CREATE INDEX idx_t_line_interval ON t USING gist (line_interval);

UPDATE t SET line_interval = 
  tstzrange(
    to_timestamp(ST_M(ST_PointN(geom,1))),
    to_timestamp(ST_M(ST_PointN(geom,ST_NPoints(geom)))));
	
EXPLAIN ANALYSE
SELECT * FROM t
WHERE line_interval && tstzrange(
	                    to_timestamp(1618566548), 
	                    to_timestamp(1618584548));

ALTER TABLE

CREATE INDEX

UPDATE 10000

QUERY PLAN

Bitmap Heap Scan on t  (cost=9.91..422.95 rows=211 width=68) (actual time=1.554..2.796 rows=10000 loops=1)

  Recheck Cond: (line_interval && '["2021-04-16 10:49:08+01","2021-04-16 15:49:08+01")'::tstzrange)

  Heap Blocks: exact=295

  ->  Bitmap Index Scan on idx_t_line_interval  (cost=0.00..9.86 rows=211 width=0) (actual time=1.520..1.520 rows=10000 loops=1)

        Index Cond: (line_interval && '["2021-04-16 10:49:08+01","2021-04-16 15:49:08+01")'::tstzrange)

Planning Time: 0.169 ms

Execution Time: 3.190 ms

EXPLAIN