Speeding up group by query in PostgreSQL 9.6.5
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.