Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc? If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course when they are not cached/buffered 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.