Inlining SQL functions in PostGIS I am defining a new data type tpointp for storing trajectories of moving objects. One function associated to this data type is atPeriod(tpointp, tsrange) which projects the full trajectory to a time period represented as a timestamp range (tsrange).

CREATE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;


This function can be used in queries like the following one

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip
-- Call the temporal index if any
WHERE getTime(Trip) &amp;&amp; Period 

Nested Loop  (cost=0.14..475.50 rows=898 width=32)
  -&gt;  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)
  -&gt;  Index Scan using berlinmod_trip_temporal_gist_idx on berlinmod_trip  (cost=0.14..2.30 rows=18 width=104)
        Index Cond: (gettime(trip) &amp;&amp; queryperiods.period)


As it is done in PostGIS, I would like to use inlining for implicitly calling any potential indexes. That would allow the user to remove the WHERE clause in the above query. I rewrote the definition of the function atPeriod() above as follows.

CREATE FUNCTION _atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'SELECT @extschema@._atPeriod($1,$2) WHERE getTime($1) OPERATOR(@extschema@.&amp;&amp;) $2'
    LANGUAGE 'sql' IMMUTABLE;


However with the new definition there is no implicit index call

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip

Nested Loop  (cost=0.00..47224.47 rows=179700 width=32)
  -&gt;  Seq Scan on berlinmod_trip  (cost=0.00..49.97 rows=1797 width=104)
  -&gt;  Materialize  (cost=0.00..3.50 rows=100 width=24)
        -&gt;  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)


In PostgreSQL there are several conditions to enable inlining SQL functions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

However, I was not able to understand why this doesn't work for the function atPeriod().

Notice that this works for boolean functions. I have another function intersects() which generalizes the ST_Intersects topological operator:

CREATE OR REPLACE FUNCTION _intersects(tpointp, geometry)
    RETURNS boolean
    AS 'MODULE_PATHNAME', 'intersects_tpointp_geom'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION intersects(tpointp, geometry)
    RETURNS boolean
    AS 'SELECT trajectory($1) OPERATOR(@extschema@.&amp;&amp;) $2 AND @extschema@._intersects($1,$2)'
    LANGUAGE 'sql' IMMUTABLE;


In this case implicit index calls are performed as shown in the following query.

EXPLAIN
SELECT *
FROM QueryRegions R, berlinmod_trip T
WHERE intersects(R.Geom, T.Trip)

Nested Loop  (cost=0.14..278.50 rows=60 width=959)
  -&gt;  Seq Scan on queryregions r  (cost=0.00..13.00 rows=100 width=847)
  -&gt;  Index Scan using berlinmod_trip_spatial_idx on berlinmod_trip t  (cost=0.14..2.65 rows=1 width=112)
        Index Cond: (r.geom &amp;&amp; trajectory(trip))
        Filter: _intersects(r.geom, trip)
I think you may be making this more complex then need be, two alternative methods of storing trajectories.


Suggested method: create a LINESTRING M with each POINT M's M dimension representing the offset since t0. This is the method that PostGIS standardizes around with ST_IsValidTrajectory, ST_DistanceCPA, and ST_AddMeasure
Normalize to a time interval with a LINESTRING: every POINT on the LINESTRING represents t0 + numpoints-1


Both of those scheme can be indexed simply and easily.