1

Given two similar tables, metrics_a and metrics_b, PostgreSQL 9.6:

create table metric_a (
    logdate date not null;
    point_id int4 not null;
    m00 numeric(9,5) not null;
    ...
    m23 numeric(9,5) not null;
    CONSTRAINT metric_a_pk PRIMARY KEY (logdate, point_id)
);
create index metric_a_point_idx on metric_a (point_id);

Each with about 150,000 point_id values with hourly measurements into m00, m01, etc. for 24 hours a day. That's about 55 million rows per year per table, and we have 10 years of data so far. There will always be only 2 metrics, a and b.

I assume I should partition by date range, which I'm working on. But in general, for queries where metrics like this are joined for at least 30 days of data, is it better to store them as:

  • one wide table -- 48 hourly columns, 24 for each metric
  • one "half-wide" table -- a point_id column and 24 hourly columns
  • one long table -- logdate, point_id, hour, metric_value
  • two tables, wide or long?
  • range partitioned by month, week, or metric? long or wide?

Of course the long table version is by far the easiest to deal with. It's much simpler to write queries to total the whole day, or compare several hours' metrics against each other. However, the table size for the long version could easily total over 13 billion rows with very inefficient use of storage.

I've tried using two wide tables and one combined 48-column wide table (neither partitioned), with little difference in performance.

Current queries look like this:

select a.point_id, count(*) tot_ct, :lim as lim,
    count(case when a.m00 <= :lim then 1 end) a_ct00,
    count(case when a.m01 <= :lim then 1 end) a_ct01,
    count(case when a.m02 <= :lim then 1 end) a_ct02,
    ...
    count(case when (a.m00 - b.m00) >= 4.25
        and b.m00 <= :lim then 1 end) w_ct00,
    count(case when (a.m01 - b.m01) >= 4.25
        and b.m01 <= :lim then 1 end) w_ct01,
    count(case when (a.m02 - b.m02) >= 4.25
        and b.m02 <= :lim then 1 end) w_ct02,
    ...
    avg(a.m00) av_a00, avg(a.m01) av_a01, avg(a.m02) av_a02, ...
    ...
from metrics_a a, metrics_b b
where a.logdate = b.logdate
    and a.point_id = b.point_id
    and a.logdate between '6/1/2017' and '7/31/2017'

The query above and most anything similar to it takes about 8 minutes to run. That's not too bad except I need to run several of these in a batch for different values of :lim and the 4.25 value and then combine and filter the results (currently in Python).

I can provide more details, but this post is already very long. Thanks for any help.

  • Where is metric_id coming from? Not in the table definition above. Always provide your version of Postgres for performance questions. – Erwin Brandstetter Sep 18 '17 at 16:44
  • Apologies. I mixed the column name up. metric_id is actually point_id. Post edited. – nsymms Sep 18 '17 at 16:52
0

If that's the complete situation I would merge metrics_a and metrics_b for considerably smaller storage and considerably faster and simpler queries.

Your example query could then look like this:

SELECT point_id
     , count(*) AS tot_ct
     , :lim AS lim
     , count(a00 <= :lim OR NULL) AS a_ct00
     , count(a01 <= :lim OR NULL) AS a_ct01
     , count(a02 <= :lim OR NULL) AS a_ct02
     -- ,  ... more
     , count((a00 - b00) >= 4.25 AND b00 <= :lim OR NULL) w_ct00
     , count((a01 - b01) >= 4.25 AND b01 <= :lim OR NULL) w_ct01
     , count((a02 - b02) >= 4.25 AND b02 <= :lim OR NULL) w_ct02
     -- ,  ... more
     , avg(a00) AS av_a00, avg(a01) AS av_a01, avg(a02) AS av_a02  -- , ... more
FROM   metrics
WHERE  logdate BETWEEN '2017-06-01' AND '2017-07-31';
  • Renamed m00 to a00 and b00 etc. respectively.
  • Saves at least 28 bytes (tuple header + item pointer) + 8 - 16 bytes for redundant columns per row. Smaller is faster, too. See:
  • No join, faster query.
  • Shorter, simpler code.

Asides:

|improve this answer|||||
  • Thanks for the response and the useful links; good stuff to know. Is range partitioning by date recommended? – nsymms Sep 18 '17 at 17:03
  • 1
    @nsymms: Sure - if it fits the use case. Look to Postgres 10, though. I recently posted example code for range partitioning by date here: dba.stackexchange.com/a/186085/3684 – Erwin Brandstetter Sep 18 '17 at 17:11

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.