MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

CREATE TABLE `pos_transactions` (
  `psp_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  KEY `psp_ind` (`psp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into pos_transactions values (1,1), (2,2), (3,3), (4,4),(5,5),(6,6),(7,7), (8,8), (9,9), (10,10);

insert into pos_transactions
select psp_id+10, amount+10
from pos_transactions;

insert into pos_transactions
select psp_id+20, amount+20
from pos_transactions;

insert into pos_transactions
select psp_id+40, amount+40
from pos_transactions;

insert into pos_transactions
select psp_id+80, amount+80
from pos_transactions;

insert into pos_transactions
select psp_id+160, amount+160
from pos_transactions;

insert into pos_transactions
select psp_id+320, amount+320
from pos_transactions;

insert into pos_transactions
select psp_id+640, amount+640
from pos_transactions;

insert into pos_transactions
select psp_id+1280, amount+1280
from pos_transactions;

explain
SELECT SUM(amount) sum, psp_id
FROM pos_transactions
GROUP BY psp_id;

create index psp_amount_ind on pos_transactions (psp_id, amount);

explain
SELECT SUM(amount) sum, psp_id
FROM pos_transactions
GROUP BY psp_id;

version()
8.0.30

Records: 10  Duplicates: 0  Warnings: 0

Records: 10  Duplicates: 0  Warnings: 0

Records: 20  Duplicates: 0  Warnings: 0

Records: 40  Duplicates: 0  Warnings: 0

Records: 80  Duplicates: 0  Warnings: 0

Records: 160  Duplicates: 0  Warnings: 0

Records: 320  Duplicates: 0  Warnings: 0

Records: 640  Duplicates: 0  Warnings: 0

Records: 1280  Duplicates: 0  Warnings: 0

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pos_transactions	null	index	psp_ind	psp_ind	5	null	2560	100.00	null

Records: 0  Duplicates: 0  Warnings: 0

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pos_transactions	null	index	psp_ind,psp_amount_ind	psp_amount_ind	10	null	2560	100.00	Using index