PostgreSQL 9.6 performance problem with Sum() aggregate query
If your table is INSERT-only, there are ways to get your sums (much) faster.

Assuming there is a column with monotonically increasing values (like id or created in your example), create a MATERIALZED VIEW to pre-compute sums older than a (recent) given threshold. And then just add the sum of recent additions to it:

CREATE MATERIALIZED VIEW shares_summed AS
SELECT poolid, miner, SUM(difficulty) AS sum_diff
FROM   shares
GROUP  BY  poolid, miner
ORDER  BY  poolid, miner;  -- optional, but to optimize some more
WHERE  created &lt;  '2018-01-01 0:0';


If there are many combinations of (poolid, miner), add a UNIQUE index on that. Also required to refresh CONCURRENTLY, see below.

Fetching a row from that table cost next to nothing. Then you only add recent additions to it:

SELECT sum(sum_diff) AS total_sum  -- takes care of possible missing rows
FROM  (
   SELECT sum_diff
   FROM   shares_summed
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created &gt;= '2018-01-01 0:0'
   ) sub;                                                   


You only have to adapt the threshold value from time to time and REFRESH the MV. Best with the CONCURRENTLY option, to avoid concurrency issues with your many INSERTs. Consider:


Analyze needed after a refresh materialized view?


Remember the value and adapt queries accordingly. You might store it in another table.

To make it even faster, create a partitioned table and add another partition with pre-computed sums after every month (or whatever). You can easily schedule a monthly cron job to automate this. Then the threshold is always the start of the month or so:

SELECT sum(sum_diff) AS total_sum
FROM  (
   SELECT SUM(difficulty) AS sum_diff
   FROM   shares_summed_master  -- includes all partitions
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created &gt;= date_trunc('month', now())  -- careful, current time zone affects it
   ) sub;           


Make sure there is no overlap, and pre-computed sums are current.

Table partitioning was largely improved and simplified with Postgres 10. Be sure to run the latest point release, some corner case bugs were fixed in Postgres 10.2. The above link to the manual is for version 9.6. Current version here.