In general for one table an index helps, but we have almost no queries on a single table. The Query Profiler is telling what part of the query takes the most (CPU, disk reads) and that is the starting point. Tuning a single query can take hours, depending how slow it is and how often it will run.
There is some information on Internet on query optimizations, look it up. There are also server side optimizations, specific to the RDBMS you use and the flexibility it has.
Are you really doing OLTP transactions on a table with millions of rows that require complex searches or are you doing OLAP style reporting?
I don’t think anyone would suggest using an ORM for reporting, aggregating, etc. I would even go so far as suggesting using a different type of database/table structure - a columnar store and send data to a reporting database. A reporting query over millions of rows wouldn’t be real time anyway.
Just keeping the lights on in manufacturing plants with thousands of sensors and counters monitoring the production. Just data collection is useless if you don't put it on good use and that requires some aggregation and correlation to tell what is going on on the floor.
My only infrastructure/data analytics experience outside of standard RDMS is with AWS so anytime I mention an AWS service feel free to substitute a non AWS/open source alternative...
For aggregation and reporting my usual go to would be Redshift - a custom AWS version of Postgres that uses columnar storage instead of rows and/or something like Kinesis (aka similar to Kafka) for streaming data and real-time aggregation while the data is being captured.
There is some information on Internet on query optimizations, look it up. There are also server side optimizations, specific to the RDBMS you use and the flexibility it has.