Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?
It is quite obvious, is it not?

If the IO side of the database is the bottleneck, then having a better IO side will speed things up.

Now, most of the time - outside of data warehouses - IO should not bottleneck that bad once you leave the really low IOPS side of Hard discs. A proper database server will have enough RAM (again, outside of data warehouses) hat a lot is cached - which means that no, you may not really need THAT much database bandwidth and it will likely NOT get you 4x the throughput.

But if you have like 5gb RAM and a 100GB table and you make a table scan, then yes, faster IO means faster query processing. Obviously.

Also Note: NVM does not necessarily mean the SSD is also faster. Not sure they are any, but you better make sure your NVM SSD is up to the task. There are some really cheap ones that are a: not optimized for the write performance needed and b: just too slow to use up the NVM ports.