Speeding up group by query in PostgreSQL 9.6.5 I have this table along with the respective indexes:

CREATE TABLE shares
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    poolid TEXT NOT NULL,
    blockheight BIGINT NOT NULL,
    difficulty DOUBLE PRECISION NOT NULL,
    networkdifficulty DOUBLE PRECISION NOT NULL,
    payoutinfo TEXT NULL,
    miner TEXT NOT NULL,
    worker TEXT NULL,
    useragent TEXT NULL,
    ipaddress TEXT NOT NULL,
    created TIMESTAMP NOT NULL
);

CREATE INDEX IDX_SHARES_POOL_BLOCK on shares(poolid, blockheight);
CREATE INDEX IDX_SHARES_POOL_MINER on shares(poolid, miner);
CREATE INDEX IDX_SHARES_POOL_CREATED ON shares(poolid, created);


... and this relatively simple query:

EXPLAIN (BUFFERS, ANALYZE) SELECT SUM(difficulty), miner, worker FROM shares 
WHERE poolid = 'xmr1' AND created &lt;= '12/18/2017' GROUP BY miner, worker;


... resulting in this execution plan:

 GroupAggregate  (cost=3276833.38..3354436.07 rows=2387929 width=60) (actual time=9241.633..11041.639 rows=342 loops=1)
   Group Key: miner, worker
   Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
   -&gt;  Sort  (cost=3276833.38..3290264.23 rows=5372340 width=60) (actual time=9234.860..10468.246 rows=2940014 loops=1)
         Sort Key: miner, worker
         Sort Method: external merge  Disk: 352784kB
         Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
         -&gt;  Bitmap Heap Scan on shares  (cost=411031.05..2272306.15 rows=5372340 width=60) (actual time=462.250..4411.353 rows=2940014 loops=1)
               Recheck Cond: ((poolid = 'xmr1'::text) AND (created &lt;= '2017-12-18 00:00:00'::timestamp without time zone))
               Rows Removed by Index Recheck: 9129527
               Heap Blocks: exact=40162 lossy=500188
               Buffers: shared hit=960 read=560857 dirtied=5837 written=7378
               -&gt;  Bitmap Index Scan on idx_shares_pool_created  (cost=0.00..409687.97 rows=5372340 width=0) (actual time=454.259..454.259 rows=2940014 loops=1)
                     Index Cond: ((poolid = 'xmr1'::text) AND (created &lt;= '2017-12-18 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=4 read=21463 written=356
 Planning time: 0.171 ms
 Execution time: 11081.661 ms
(17 rows)


Two questions: 


Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?
What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement
Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?


It didn't. You read the query from the inside out. Not from the top down.


  What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement


Well, you have two in addition to indexing.


Your estimates are off. Pg expects 5.3 M rows, you return 2.9 M.
Your sort of 2.9 M rows is going to disk. If you can't eliminate the sort, you may want to look at adding more work_mem.


Try these in this order.


VACUUM FULL ANALYZE shares; and try again. See if you get a better estimate.
Drop all of the index you've got. Try this

CREATE INDEX zzz1 ON shares (poolid, created, miner, worker, difficulty);
ANALYZE shares;
CLUSTER shares ON zzz1;

See how that performs. You may want to go BRIN, your table is getting kind of big. Check into it anyway.


If you can normalize xml1, I would do that too. (Example 1::int and keep the xml elsewhere.) You should also check into moving to timestamp with time zone. You almost never want timestmap without time zone.