add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 trail
(
time timestamptz NOT NULL default now(),
user_id int not null,
position geography(POINT, 4326)
);

INSERT INTO trail VALUES
('2012-01-03T12:42:23', 1, ST_GeomFromText('POINT(-71.0 48.4)', 4326)),
('2012-01-03T12:54:23', 1, ST_GeomFromText('POINT(-72.0 48.4)', 4326)),
('2012-01-03T12:59:23', 1, ST_GeomFromText('POINT(-72.0 50.4)', 4326)),
('2012-02-03T12:42:23', 1, ST_GeomFromText('POINT(-71.0 48.4)', 4326)),
('2012-02-03T12:54:23', 1, ST_GeomFromText('POINT(-72.0 48.4)', 4326)),
('2012-02-03T12:59:23', 1, ST_GeomFromText('POINT(-72.0 50.4)', 4326)),
('2012-02-03T12:42:23', 2, ST_GeomFromText('POINT(-61.0 28.4)', 4326)),
('2012-02-03T12:54:23', 2, ST_GeomFromText('POINT(-62.0 28.4)', 4326)),
('2012-02-03T12:59:23', 2, ST_GeomFromText('POINT(-62.0 30.4)', 4326));

SELECT * FROM trail
CREATE TABLE

INSERT 0 9
time user_id position
2012-01-03 12:42:23+00 1 0101000020E61000000000000000C051C03333333333334840
2012-01-03 12:54:23+00 1 0101000020E610000000000000000052C03333333333334840
2012-01-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940
2012-02-03 12:42:23+00 1 0101000020E61000000000000000C051C03333333333334840
2012-02-03 12:54:23+00 1 0101000020E610000000000000000052C03333333333334840
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940
2012-02-03 12:42:23+00 2 0101000020E61000000000000000804EC06666666666663C40
2012-02-03 12:54:23+00 2 0101000020E61000000000000000004FC06666666666663C40
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 
SELECT 9
SELECT
*,
lag(position) OVER (PARTITION BY user_id ORDER BY time) as prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours'
time user_id position prev_position
2012-02-03 12:42:23+00 1 0101000020E61000000000000000C051C03333333333334840 null
2012-02-03 12:54:23+00 1 0101000020E610000000000000000052C03333333333334840 0101000020E61000000000000000C051C03333333333334840
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940 0101000020E610000000000000000052C03333333333334840
2012-02-03 12:42:23+00 2 0101000020E61000000000000000804EC06666666666663C40 null
2012-02-03 12:54:23+00 2 0101000020E61000000000000000004FC06666666666663C40 0101000020E61000000000000000804EC06666666666663C40
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 0101000020E61000000000000000004FC06666666666663C40 
SELECT 6
SELECT
*,
ST_Distance(
position,
lag(position) OVER (PARTITION BY user_id ORDER BY time)
) as distance_to_prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours'
time user_id position distance_to_prev_position
2012-02-03 12:42:23+00 1 0101000020E61000000000000000C051C03333333333334840 null
2012-02-03 12:54:23+00 1 0101000020E610000000000000000052C03333333333334840 74046.12952794
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940 222434.92794088
2012-02-03 12:42:23+00 2 0101000020E61000000000000000804EC06666666666663C40 null
2012-02-03 12:54:23+00 2 0101000020E61000000000000000004FC06666666666663C40 97995.98031436
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 221684.90508034 
SELECT 6
SELECT
*,
SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
SELECT
*,
ST_Distance(
position,
lag(position) OVER (PARTITION BY user_id ORDER BY time)
) as distance_to_prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours'
) s
time user_id position distance_to_prev_position cum_distance
2012-02-03 12:42:23+00 1 0101000020E61000000000000000C051C03333333333334840 null null
2012-02-03 12:54:23+00 1 0101000020E610000000000000000052C03333333333334840 74046.12952794 74046.12952794
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940 222434.92794088 296481.05746882
2012-02-03 12:42:23+00 2 0101000020E61000000000000000804EC06666666666663C40 null null
2012-02-03 12:54:23+00 2 0101000020E61000000000000000004FC06666666666663C40 97995.98031436 97995.98031436
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 221684.90508034 319680.8853947 
SELECT 6
SELECT DISTINCT ON (user_id)
*,
SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
SELECT
*,
ST_Distance(
position,
lag(position) OVER (PARTITION BY user_id ORDER BY time)
) as distance_to_prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours'
) s
ORDER BY user_id, time DESC
time user_id position distance_to_prev_position cum_distance
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940 222434.92794088 296481.05746882
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 221684.90508034 319680.8853947 
SELECT 2
CREATE MATERIALIZED VIEW distance_24 AS
SELECT DISTINCT ON (user_id)
*,
SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
SELECT
*,
ST_Distance(
position,
lag(position) OVER (PARTITION BY user_id ORDER BY time)
) as distance_to_prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours'
) s
ORDER BY user_id, time DESC;

SELECT * FROM distance_24
SELECT 2
time user_id position distance_to_prev_position cum_distance
2012-02-03 12:59:23+00 1 0101000020E610000000000000000052C03333333333334940 222434.92794088 296481.05746882
2012-02-03 12:59:23+00 2 0101000020E61000000000000000004FC06666666666663E40 221684.90508034 319680.8853947 
SELECT 2