I have a situation where there are network hosts whose monthly bandwidth is summed based on a start date that varies for each host.

The number of hosts is quite small (< 5000) but the history table where bandwidth is stored has hundreds of millions of rows

I have a query that works and uses an index on the history table, but it's still very slow.

My questions: Is there a way I can redo my query (and possibly add an index) that will substantially improve performance? Is there a clever way in which I can restructure my tables to allow me to efficiently sum usage based on based on start and stop times.

my_db=> \dS+ history_uints
                                 Table "public.history_uints"
   Column   |            Type             | Modifiers | Storage  | Stats target | Description
 hostid     | bigint                      | not null  | plain    |              |
 value      | bigint                      |           | plain    |              |
 type       | character varying           |           | extended |              |
 day        | integer                     |           | plain    |              |
 month      | integer                     |           | plain    |              |
 year       | integer                     |           | plain    |              |
 created_at | timestamp without time zone | not null  | plain    |              |
 updated_at | timestamp without time zone | not null  | plain    |              |
 clock      | integer                     | not null  | plain    |              |
    "history_uints_no_dups_idx" UNIQUE, btree (hostid, type, day, month, year)
    "index_history_uints_on_clock_and_type" btree (clock, type)
    "index_history_uints_on_hostid" btree (hostid)
    "index_history_uints_on_hostid_and_type_and_clock" btree (hostid, type, clock)
    "index_history_uints_on_month_and_year_and_type" btree (month, year, type)

my_db=>  \dS+ throttle_start
                  Table "pg_temp_7.throttle_start"
 Column |  Type   | Modifiers | Storage | Stats target | Description
 hostid | bigint  |           | plain   |              |
 start  | integer |           | plain   |              |
 stop   | integer |           | plain   |              |
    "th_start_hostid_idx" UNIQUE, btree (hostid)

my_db=> explain analyze select
  , (
      hostid = ts.hostid
      AND clock BETWEEN ts.start AND ts.stop
      AND type = 'DailyTotalUsage'
    ) "value"
  throttle_start ts
                                                                                    QUERY PLAN
 Seq Scan on throttle_start ts  (cost=0.00..101143.71 rows=4885 width=16) (actual time=1.744..20140.864 rows=4885 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=20.64..20.66 rows=1 width=8) (actual time=4.115..4.115 rows=1 loops=4885)
           ->  Index Scan using index_history_uints_on_hostid_and_type_and_clock on history_uints  (cost=0.56..20.63 rows=4 width=8) (actual time=2.216..4.064 rows=27 loops=4885)
                 Index Cond: ((hostid = ts.hostid) AND ((type)::text = 'DailyTotalUsage'::text) AND (clock >= ts.start) AND (clock <= ts.stop))
 Planning time: 0.438 ms
 Execution time: 20144.532 ms
  • for most databases a lot of index scanning is typically slower than doing a single table scan (especially if using a parallel table scan), so what I'm wondering is if there's a single start/stop data pair that could be used to table scan the history table, then join the (aggregated results) to your throttle_start table; I know, I know, I know ... you mention 'different' start/stop for each host but ... if you're doing a monthly rollup, how 'different' can these start/stop dates really be? it would probably help to see some sample data ... ??? – markp Sep 20 '18 at 13:34
  • Try a lateral join: rextester.com/OYR49163 – a_horse_with_no_name Sep 20 '18 at 13:36
  • The monthly rollup is more of a historical relic. Usage used to be measured by the month but now it's measured based on a start date which can be any day from the first of the month to the last day of the month. Some hosts have window of Sept 1 to Oct 1 others have a window of Sept 27 to Oct 27 etc. – sheepdog Sep 20 '18 at 13:38
  • @a_horse_with_no_name I'm seeing similar performance with a lateral join. Should I post the explain analyze output in a comment? – sheepdog Sep 20 '18 at 13:46
  • What about a filtered aggregate? rextester.com/WSHD76401 – a_horse_with_no_name Sep 20 '18 at 13:53

Try this:

select hs.hostid, sum(value)::float/1024/1024/1024 from 
 throttle_start ts
 join history_uints hs on hs.hostid = ts.hostid 
 where clock BETWEEN ts.start AND ts.stop and type = 'DailyTotalUsage'
 group by hs.hostid; 
  • Still quite a bit slower than the original query or the lateral join. – sheepdog Sep 20 '18 at 14:17

You could add "value" onto the end of the index "index_history_uints_on_hostid_and_type_and_clock", so that it can do an index only scan.

Other than that, how fast do you really need this query to be? It looks like something you will run once per day at most. You could make a materialized view to store the aggregates if you need the output of this query very often.

Are start and stop always aligned to day or hour boundaries, or can they be totally arbitrary to millisecond precision?


Try this:

create index history_uints_hostid_value_clock_daily on history_uints(hostid, clock, value) where type = 'DailyTotalUsage'
  hs.hostid, sum(value)::float/1024/1024/1024
from history_uints hs
join throttle_start ts
  on hs.hostid = ts.hostid
  clock BETWEEN ts.start AND ts.stop
  and type = 'DailyTotalUsage'
group by hs.hostid

By this, the big table history will be scan 1 time only.

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.