I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS:
- an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about 50 million records per week (80 records per second), with about 2 weeks of data so far (100 million records). This table is used to retrieve the historical positions of any individual sensor
- a Current Positions table, which stores the latest reading from any sensor by it's primary key. An update trigger updates which row stores the latest reading from a sensor.
Definition for All Positions:
CREATE TABLE all_positions ( pk BIGSERIAL PRIMARY KEY, sensor_id UUID, rcv_tme TIMESTAMP WITHOUT TIME ZONE, -- many other fields... ) CREATE TABLE all_positions_y2016w01 ( CHECK (rcv_tme >= '2016-01-04 00:00:00'::timestamp without time zone AND rcv_tme < '2016-01-11 00:00:00'::timestamp without time zone) ) INHERITS (all_positions); CREATE UNIQUE INDEX all_positions_y2016w01_pk_index ON all_positions_y2016w01(pk) CREATE TABLE all_positions_y2016w02 ( CHECK (rcv_tme >= '2016-01-11 00:00:00'::timestamp without time zone AND rcv_tme < '2016-01-18 00:00:00'::timestamp without time zone) ) INHERITS (all_positions); CREATE UNIQUE INDEX all_positions_y2016w02_pk_index ON all_positions_y2016w02(pk) -- ... other weekly tables -- upserts into current_position table the latest pk for a sensor_id CREATE TRIGGER insert_positions_current_trigger BEFORE INSERT ON all_positions FOR EACH ROW EXECUTE PROCEDURE insert_positions_current_function(); -- bins into appropriate child table CREATE TRIGGER insert_positions_partition_trigger BEFORE INSERT ON all_positions FOR EACH ROW EXECUTE PROCEDURE insert_positions_partition_function();
and Current Positions:
CREATE TABLE current_positions ( sensor_id UUID, pk BIGINT ) CREATE UNIQUE INDEX current_positions_sensorid_idx on current_positions(sensor_id) CREATE UNIQUE INDEX current_positions_pk_idx on current_positions(pk)
The issue is when I perform the following query:
EXPLAIN ANALYZE SELECT t1.* FROM current_positions t2 INNER JOIN all_positions t1 ON t1.pk = t2.pk
it performs a seq scan of all child tables i.e. 100 million rows which takes 7 minutes.
But if I perform the following query (assuming that only week 1 and week 2 hold all the records):
EXPLAIN ANALYZE (SELECT t1.* FROM current_positions t2 INNER JOIN all_positions_y2016w01 t1 ON t1.pk = t2.pk) UNION (SELECT t1.* FROM current_positions t2 INNER JOIN all_positions_y2016w02 t1 ON t1.pk = t2.pk)
it performs an index scan on the child tables, and takes about 2 seconds.
I believe this is because the tables are partitioned by time, and PostgreSQL doesn't understand that the pk column is unique across all the child tables i.e. for each (sensor_id, pk) record in current_positions, there will only be one corresponding record which matches pk across all the all_position child tables. Is there any way to force an index scan on the child tables, instead of a seq scan?
Note: I've linked the two EXPLAIN ANALYZE below