We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 million records. Now we usually make queries like:
select E from Table X where A=.. and B=..
depending on different data patterns, sometimes this query results in 0 or 1 record response and sometimes even more than 50,000 results.
When combination of A and B is frequent, queries take too much time to reply us.
Of course we can use Count(*) or Count() or Count (1) to find number of results without the need to fetch all 50,000 possible answers.
But usually such all queries take between 500ms to even 28000ms...
What we need to achieve is to be able to find a simple HIT answer to know number of possible results for above query in less than 5ms. we make queries to this table like 200 times per second for our application, so we need to make sure if such queries has any result or not..and if yes, what is the number of results.
Usually in Big data database performances, there is a keyword like HIT which means how many possible answers are available for a query.
We need exactly similar thing in postgres and we dont know if any function, extension or module exists that we can install to achieve hit results in a fraction of mili seconds. I am sure there must be a solution for that, but still we are looking for it. I mention again that we only need to know if query has any result and number of results, we don't need to fetch the results.