1

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),
  recieved 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_recieved_station_id_idx
  ON public.de_tt_priceinfo (recieved, 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, recieved FROM de_tt_priceinfo a 
WHERE a.recieved = (SELECT MAX(recieved) FROM de_tt_priceinfo b 
  WHERE a.station_id = b.station_id
  AND  recieved <= '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.

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, recieved
   FROM   de_tt_priceinfo
   WHERE  recieved <= '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, recieved DESC NULLS LAST
   LIMIT 1  
   )
   UNION ALL
   (
   SELECT u.station_id, u.e5, u.e10, u.diesel, u.recieved
   FROM   cte c
   JOIN de_tt_priceinfo u ON u.recieved > c.recieved   
   WHERE  u.recieved <= '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.recieved 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
  • For performance questions like this, the EXPLAIN output as instructed here is instrumental. – Erwin Brandstetter Feb 18 '15 at 1:50
  • @ErwinBrandstetter added the information requested – Benjamin Feb 18 '15 at 2:07
  • I assume the three numeric(4,3) columns e5, e10 and diesel are prices with a precision of 0.1 Cent? – Erwin Brandstetter Feb 18 '15 at 2:27
  • They are german fuel prices, which look like 1,319 Euro/l. The original data came from a mySQL database and was migrated to postgresql, thats why numeric(4,3) is used. To me its no difference to convert it to a different format, as long as I do not lose any precision. The table design is not mine, but was given so it is as it is. – Benjamin Feb 18 '15 at 2:31
  • Can they be NULL? – Erwin Brandstetter Feb 18 '15 at 2:34
1

Note: I replaced recieved with received everywhere.

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:

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:

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 pointer), 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:

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:

  • FEHLER: Unique Index „de_tt_priceinfo_station_id_recieved“ could not be created SQL Status:23505 Detail:key (station_id, recieved)=(871828b4-37e5-419c-b7a5-cdbe1e1c0148, 2014-06-12 10:36:20+02) is duplicated. (translated msg) – Benjamin Feb 18 '15 at 2:41
  • I will check the original data tomorrow. Maybe this is an conversion error. – Benjamin Feb 18 '15 at 2:42
  • @Benjamin: If the combination (station_id, received) is not unique, create a plain index instead. I suspect an error in your data though (which is why the constraint has the additional benefit of disallowing such errors in the future. – Erwin Brandstetter Feb 18 '15 at 2:56
  • The original data also has the error, but its just one so im gonna delete the one with the lower id :) The Station ID "should" be a UUID but has various lengths. I will try to test if I can convert it to a UUID but i doubt it. – Benjamin Feb 18 '15 at 23:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.