-- create database
CREATE DATABASE test;
\c test

-- set timing
\timing

-- create table
CREATE TABLE updates (
id serial,
driver_id int not null,
latitude double precision not null,
longitude double precision not null,
"time" timestamp with time zone not null default now(),
vehicle_id integer);

-- populate table with 99 homogeneus distributed values
INSERT INTO updates SELECT q, q % 99, q, q, to_timestamp(q), q % 99 FROM generate_series(1, 15000000) q;
-- populate table with 1 value with 6M rows, this data will be 9M records old, under 'time' field point of view .
INSERT INTO updates SELECT q + 15000000, 100, q, q, to_timestamp(q), -- timestamp will start at 1 at end at 6M
	100 FROM generate_series(1, 6000000) q;

-- add constraints and indexes
ALTER TABLE updates ADD PRIMARY KEY (id);
CREATE INDEX ON updates ("time");
CREATE INDEX ON updates (driver_id, "time");
CREATE INDEX ON updates (vehicle_id, "time");

-- check behavior 
ANALYZE updates;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1;

-- drop time index and rerun queries
DROP INDEX updates_time_idx ;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1;

