-- create database
CREATE DATABASE test;
\c test

-- set timing
\timing on

-- 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 500K rows, simmilar distribution you posted.
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, 500000) q;

-- add constraints and indexes
ALTER TABLE updates ADD PRIMARY KEY (id);
CREATE INDEX ON updates ("time");
CREATE INDEX ON updates (vehicle_id, "time");
-- create 'bloated' driver_id, time index.
CREATE INDEX ON updates (driver_id, "time") WITH (fillfactor = 10);

-- check index sizes, updates_driver_id_idx is huge.
SELECT relname, relpages FROM pg_class WHERE relname LIKE 'updates%';

-- check behavior with bloated index
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;

-- rebuild index with default fillfactor
ALTER INDEX updates_driver_id_time_idx SET (fillfactor = 90);
REINDEX INDEX updates_driver_id_time_idx;

-- recheck index sizes, updates_driver_id_idx should look pretty simmilar to others.
SELECT relname, relpages FROM pg_class WHERE relname LIKE 'updates%';

-- check behavior with regular sized index
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;

