Open

Optimize Postgress DB having nearly 150 mil records

I'm using Azure Postgress DB to store around 150 mil records. Specs of the DB are: 160GB storage and around 4GB RAM. The Dataset is around 80GB when loaded into the DB. The data set will not going to grow but will remain around 80 - 85 GB.

Most of the queries are based on 4 fields:

Address (text) 
Longitude (double)
Latitude (double)
PropertyId (big int)

I've implemented indexes on all these fields. For the Address - GIN, others B-Tree.

But the problem is still the queries are slow i.e.

select * from my_table 
where Latitude between x and y
and Longitude between p and q
and address like '%address%';

takes minutes...

I analysed the queries using explain analyse, which shows the queries indeed use indexes.

So my question is, are there any ways to improve the performance (SQL wise)? Is there a rule of thumb when calculating the memory and storage requirements given the data size? what could be the minimum adequate hardware requirements to get results under 2 seconds?