Finding the current prices for n Fuelstations at a certain point in time

Question

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.

CREATE TABLE public.de_tt_priceinfo (
  id integer NOT NULL DEFAULT nextval('priceinfo_id_seq'::regclass),
  station_id character varying(60),
  received timestamp with time zone NOT NULL DEFAULT now(),
  e5 numeric(4,3),
  e10 numeric(4,3),
  diesel numeric(4,3),
  CONSTRAINT de_tt_priceinfo_id_pkey PRIMARY KEY (id)
);

CREATE INDEX de_tt_priceinfo_received_station_id_idx
  ON public.de_tt_priceinfo (received, station_id COLLATE pg_catalog."default");

CREATE INDEX index_station_id
  ON public.de_tt_priceinfo (station_id COLLATE pg_catalog."default");

From this table I need to extract the latest prices at a certain point in time with maximum performance, since I have to simulate 32 million commuters which query this table (not at once, but still).

I have a working query!

SELECT station_id, e5, e10, diesel, received FROM de_tt_priceinfo a 
WHERE a.received = (SELECT MAX(received) FROM de_tt_priceinfo b 
  WHERE a.station_id = b.station_id
  AND  received <= '2014-09-25 08:45:12'::TIMESTAMPTZ)
AND station_id IN('0C91A93A-a-b-c-d', '578C44BB-a-b-c-d', '6F2F48A8-a-b-c-d'
                , '9982BE74-a-b-c-d', 'A24C612B-a-b-c-d', 'BEC3EF55-a-b-c-d'
                , 'F5137488-a-b-c-d')

The performance of this Query is not usable. Execution time is varying around 900ms. The result looks like this

0C91A93A-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:17:50.000000"
578C44BB-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:00:09.000000"
6F2F48A8-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 07:08:57.000000"
9982BE74-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:29:55.000000"
A24C612B-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:00:09.000000"
BEC3EF55-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 06:53:49.000000"
F5137488-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 07:44:55.000000"

So I searched around a little bit and found buzz words like recursive CTE, loose indexscan and some answers on DBA which seemed very close, but I could not modify them to my needs.

• How do I efficiently get "the most recent corresponding row"?
• GROUP BY one column, while sorting by another in PostgreSQL

If I understood right, a recursive CTE would be the quickest way to query the data I want.

What I got so far is this:

WITH RECURSIVE cte AS (
   (
   SELECT station_id, e5, e10, diesel, received
   FROM   de_tt_priceinfo
   WHERE  received <= '2014-09-25 08:45:00'::TIMESTAMPTZ
     AND station_id IN('0C91A93A-a-b-c-d', '578C44BB-a-b-c-d', '6F2F48A8-a-b-c-d'
                     , '9982BE74-a-b-c-d', 'A24C612B-a-b-c-d', 'BEC3EF55-a-b-c-d'
                     , 'F5137488-a-b-c-d')
   ORDER  BY station_id, received DESC NULLS LAST
   LIMIT 1  
   )
   UNION ALL
   (
   SELECT u.station_id, u.e5, u.e10, u.diesel, u.received
   FROM   cte c
   JOIN de_tt_priceinfo u ON u.received > c.received   
   WHERE  u.received <= '2014-09-25 08:45:00'::TIMESTAMPTZ  -- repeat condition!
     AND u.station_id IN('0C91A93A-a-b-c-d', '578C44BB-a-b-c-d', '6F2F48A8-a-b-c-d'
                       , '9982BE74-a-b-c-d', 'A24C612B-a-b-c-d', 'BEC3EF55-a-b-c-d'
                       , 'F5137488-a-b-c-d')
   ORDER BY u.station_id, u.received DESC NULLS LAST LIMIT 1
   )
   )
SELECT * FROM cte;

But this just returns the following two lines:

0C91A93A-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:17:50.000000"
9982BE74-a-b-c-d, 1.xxx, 1.xxx, 1.xxx, "2014-09-25 08:29:55.000000"

Update:

• SELECT Version(); PostgreSQL 9.5devel on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.8.3 p1.1, pie-0.5.9) 4.8.3, 64-bit
• EXPLAIN ANALYSE: http://explain.depesz.com/s/clrZ
• XEON 1231v3, 16 GB Ram, Samsung 840 PRO SSD
• Changes to the default postgresql.conf

# Connection
listen_addresses = '*'
max_connections = 16

# Logging
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 500ms
#log_checkpoints = on
#log_connections = on
#log_disconnections = on
log_lock_waits = on
#log_temp_files = 0

# Memory
shared_buffers = 1GB
temp_buffers = 32MB
work_mem = 256MB
maintenance_work_mem = 1GB
effective_cache_size = 8GB

# Checkpoint ( When to write to disk )
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
checkpoint_segments = 32

random_page_cost = 1.1

# Import only!
#autovacuum = off
fsync = off
synchronous_commit = off
full_page_writes = off

Answer 1

Index

First and foremost, for your type of query this is the much better index:

CREATE INDEX de_tt_priceinfo_received_station_id_idx
  ON public.de_tt_priceinfo (station_id, received);  -- note the reversed order

Since the combination is supposed to be unique (I assume), I suggest a UNIQUE constraint on (station_id, receved) instead:

ALTER TABLE de_tt_priceinfo ADD CONSTRAINT de_tt_priceinfo_station_id_received
UNIQUE (station_id, received);

The index index_station_id is mostly superseded and can probably be dropped now.
The index de_tt_priceinfo_received_station_id_idx may still have its use.

Be sure to understand the logic behind all this:

• Multicolumn index and performance
• Is a composite index also good for queries on the first field?

Query

I would also consider the basic DISTINCT ON query:

SELECT DISTINCT ON (station_id)
       station_id, e5, e10, diesel, received
FROM   de_tt_priceinfo
WHERE  received <= '2014-09-25 08:45:12'::TIMESTAMPTZ
AND    station_id = ANY ('{0C91A93A-a-b-c-d, 578C44BB-a-b-c-d, 6F2F48A8-a-b-c-d
                         , 9982BE74-a-b-c-d, A24C612B-a-b-c-d, BEC3EF55-a-b-c-d
                         , F5137488-a-b-c-d}'::varchar[])
ORDER BY station_id, received DESC;

But since you seem to have a lot of rows per station, that's not going to shine. Instead:

SELECT *
FROM  (
   VALUES
     ('0C91A93A-a-b-c-d'::varchar)
    , ('578C44BB-a-b-c-d')
    , ('6F2F48A8-a-b-c-d')
    , ('9982BE74-a-b-c-d')
    , ('A24C612B-a-b-c-d')
    , ('BEC3EF55-a-b-c-d')
    , ('F5137488-a-b-c-d')
   ) s(station_id)
LEFT JOIN LATERAL (
    SELECT e5, e10, diesel, received
    FROM   de_tt_priceinfo
    WHERE  station_id = s.station_id
    AND    received <= '2014-09-25 08:45:12'::TIMESTAMPTZ
    ORDER  BY received DESC
    LIMIT  1
   )  p ON TRUE

This one should be dynamite in combination with above UNIQUE constraint (or an equivalent index).

Detailed explanation:

• Optimize GROUP BY query to retrieve latest record per user
• Select first row in each GROUP BY group?

Table definition

For a table with millions of rows it pays to optimize storage while easily possible. Makes everything smaller and faster.

That's how I would design it:

CREATE TABLE station (
   station_id serial PRIMARY KEY
 , station    text
 , CHECK (length(station) < 61) -- ?? optional, you decide 
);

CREATE TABLE priceinfo (
   priceinfo_id serial PRIMARY KEY
 , station_id   integer NOT NULL REFERENCES station ON UPDATE CASCADE
 , received     timestamptz NOT NULL DEFAULT now(),
 , e5           integer  -- price in 0.1 Cent
 , e10          integer  -- price in 0.1 Cent
 , diesel       integer  -- price in 0.1 Cent
 , CONSTRAINT priceinfo_station_id_received UNIQUE (station_id, received)
);

CREATE INDEX priceinfo_received_idx ON public.priceinfo (received);

The row size in priceinfo would be 60 bytes (24 heap tuple header + null bitmap; 32 bytes data; 4 bytes item identifier), as compared to 94 bytes (24 + 66 + 4) in your original table. That's assuming 16-character string like in your example. Everything will be ~ 36 % smaller (or more?) and considerably faster.

The crucial index on (station_id, received) is down to 8 bytes of data per index tuple instead of 32 bytes or even much more (!) - each plus overhead. In addition, handling integer numbers for station_id is generally faster than text with a COLLATION on top of it.

Details:

• Configuring PostgreSQL for read performance

Query would fetch station_id from station table first, which is cheap.

Prices are stored as integer numbers signifying 0.1 Cent. (4 bytes instead of 10 bytes for your original numeric(4,3) Multiply with 0.1 to get Cent or 0.001 to get € for display. Very simple and fast.

UUID

The string in the error message looks considerably longer and actually like a regular UUID number:

871828b4-37e5-419c-b7a5-cdbe1e1c0148

If so, use the uuid data type. Whether you adopt my design of keep your old. At least switch to the uuid data type for a big overall gain in every aspect:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars