1

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?

Thanks!

Note: I've linked the two EXPLAIN ANALYZE below

http://explain.depesz.com/s/qvJj

http://explain.depesz.com/s/fFwr

  • Would defining a foreign key constraint give enough of a hint to the planner? – Chris Harrington Jan 17 '16 at 19:01
  • I've considered that, but apparently it's not possible in Postgres stackoverflow.com/questions/24360312/… – Justin Wong Jan 18 '16 at 2:37
  • Is it possible for you to use CREATE TABLE LIKE instead of inheriting the master table? It seems like INHERITS is most useful if you're either changing the parent tables, or inheriting from multiple parents. – Chris Harrington Jan 18 '16 at 3:39
  • Inheritance is also used for partitioning and constraint exclusion, which I require for some my queries: postgresql.org/docs/9.4/static/ddl-partitioning.html – Justin Wong Jan 18 '16 at 4:04
  • Ah, that's unfortunate, since constraint exclusion is the opposite of what you want in this instance. You might also be able to work around this by creating a view (with the working SQL) and updating it at the same time you create a new partition. Postgres helpfully offers CREATE OR REPLACE VIEW here. – Chris Harrington Jan 18 '16 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.