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.

  • Have you read the docs on Partitioning? – Eli May 14 '19 at 21:58
  • Due to the nature of Postgres' MVCC model, getting the exact number of results is not much cheaper than getting all results. You may be able to do quite a bit with smart multicolumn / partial indexes (or some other index feature), depending on exact query patterns, write load, data distribution ... but counting speed is not among Postgres' fortes. – Erwin Brandstetter May 14 '19 at 23:07
  • In any SQL database you cannot know the size of the result set until you fetch every last one of its rows. The question now is, does it matter to you if you know that your result set is exactly 12346597 rows, or 12000K is good enough? – mustaccio May 15 '19 at 3:29
  • @mustaccio Thanks for comment. We don't need exact number. Rough estimation is enough. If you have any Technic to get rough estimation in less than 5 mili seconds, please let us know.. if Postgresql is not best at doing such answers, which database technology is perfect for such queries. – Alireza.K May 15 '19 at 8:38
  • @Eli we had done investigation on partitioning before. But I do not know how partitioning can help to get number of estimated results in less than 5 mili seconds. We dont know what is incoming query to know how to classify and partition data beforehand...so please clarify your idea. – Alireza.K May 15 '19 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.