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?
0 Replies