Postgres 10

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 locations
(
    location_id serial PRIMARY KEY,
    latitude VARCHAR (10),
    longitude VARCHAR (10),
    generated_at TIMESTAMP
);

INSERT INTO locations (latitude, longitude, generated_at)
VALUES
('51.123456', '-1.123456', '2020-04-27 17:00:00.000'),
('51.123456', '-1.123456', '2020-04-27 17:05:00.000'),
('51.654321', '-1.654321', '2020-04-27 17:10:00.000'),
('51.654321', '-1.654321', '2020-04-27 17:15:00.000'),
('51.654321', '-1.654321', '2020-04-27 17:20:00.000'),
('51.444555', '-1.444555', '2020-04-27 17:25:00.000'),
('51.123456', '-1.123456', '2020-04-27 17:30:00.000'),
('51.123456', '-1.123456', '2020-04-27 17:35:00.000'),
('51.123456', '-1.123456', '2020-04-27 17:40:00.000'),
('51.444555', '-1.444555', '2020-04-27 17:45:00.000'),
('51.444555', '-1.444555', '2020-04-27 17:50:00.000'),
('51.654321', '-1.654321', '2020-04-27 17:55:00.000');

CREATE TABLE



INSERT 0 12


SELECT
    latitude,
    longitude,
    arrived_at
FROM (
    SELECT l.latitude,
    l.longitude,
    l.generated_at AS arrived_at,
    LAG (latitude) OVER w AS lagLatitude,
    LAG (longitude) OVER w AS lagLongitude
    from locations l
    WINDOW w as (ORDER BY generated_at) 
) x WHERE
    latitude is distinct from lagLatitude
or longitude is distinct from lagLongitude;


latitude
longitude
arrived_at


51.123456
-1.123456
2020-04-27 17:00:00

51.654321
-1.654321
2020-04-27 17:10:00

51.444555
-1.444555
2020-04-27 17:25:00

51.123456
-1.123456
2020-04-27 17:30:00

51.444555
-1.444555
2020-04-27 17:45:00

51.654321
-1.654321
2020-04-27 17:55:00



SELECT 6


WITH base_cte AS (
    SELECT 
        latitude,
        longitude,
        generated_at AS arrived_at,
        LAG (latitude) OVER w AS lagLatitude,
        LAG (longitude) OVER w AS lagLongitude
    FROM locations
    WINDOW w AS (ORDER BY generated_at)
)
SELECT
    latitude,
    longitude,
    arrived_at
FROM base_cte WHERE
    latitude IS DISTINCT FROM lagLatitude
    or longitude IS DISTINCT FROM lagLongitude;


latitude
longitude
arrived_at


51.123456
-1.123456
2020-04-27 17:00:00

51.654321
-1.654321
2020-04-27 17:10:00

51.444555
-1.444555
2020-04-27 17:25:00

51.123456
-1.123456
2020-04-27 17:30:00

51.444555
-1.444555
2020-04-27 17:45:00

51.654321
-1.654321
2020-04-27 17:55:00



SELECT 6


WITH grouped_cte AS (
    WITH base_cte AS (
        SELECT 
            latitude,
            longitude,
            generated_at AS arrived_at,
            LAG (latitude) OVER w AS lagLatitude,
            LAG (longitude) OVER w AS lagLongitude
        FROM locations
        WINDOW w AS (ORDER BY generated_at)
    )
    SELECT
        latitude,
        longitude,
        arrived_at
    FROM base_cte WHERE
        latitude IS DISTINCT FROM lagLatitude
        or longitude IS DISTINCT FROM lagLongitude
    )
SELECT
    latitude,
    longitude,
    arrived_at,
    LEAD(arrived_at, 1) OVER (
        ORDER BY arrived_at
    ) left_at
FROM grouped_cte;


latitude
longitude
arrived_at
left_at


51.123456
-1.123456
2020-04-27 17:00:00
2020-04-27 17:10:00

51.654321
-1.654321
2020-04-27 17:10:00
2020-04-27 17:25:00

51.444555
-1.444555
2020-04-27 17:25:00
2020-04-27 17:30:00

51.123456
-1.123456
2020-04-27 17:30:00
2020-04-27 17:45:00

51.444555
-1.444555
2020-04-27 17:45:00
2020-04-27 17:55:00

51.654321
-1.654321
2020-04-27 17:55:00
null



SELECT 6

latitude	longitude	arrived_at
51.123456	-1.123456	2020-04-27 17:00:00
51.654321	-1.654321	2020-04-27 17:10:00
51.444555	-1.444555	2020-04-27 17:25:00
51.123456	-1.123456	2020-04-27 17:30:00
51.444555	-1.444555	2020-04-27 17:45:00
51.654321	-1.654321	2020-04-27 17:55:00