How to improve the performance of counting all rows

Question

I use the SQL like this :

TableA have 2 million records, search the count speed 25.1secs.

select count(*) from TableA;

TableA have 2 million records, TableB also have 2 million records, get the count of TableA and TableB speed 3mins.

How to Increase the search efficiency?

So what is your query to "get the count of tablea and tableb"? 25 seconds for just 2 million rows seems awfully slow. Please **[EDIT]** your question and add the `create table` statements for both tables (including all indexes) and the execution plan generated using **`explain (analyze, verbose)`** both queries. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question do not post code in comments — , Jun 01 '17 at 05:51

score 2 · Answer 1 · answered Jun 01 '17 at 06:01

2

Many different ways to achieve this:

Involving triggers (a rather lengthier approach, but an efficient one)

Getting close estimate rather than getting the exact count speeds up the process.

answered Jun 01 '17 at 06:01

Akash Mishra

score 1 · Answer 2 · answered Jun 01 '17 at 05:56

Do you really need to have exact precision for the number of rows ? If not, you can use the pg_stat_user view that will give you an good estimation.

 SELECT schemaname,relname,n_live_tup 
 FROM pg_stat_user_tables 
 WHERE relname='TableA'
 AND schemaname='yourSchema'

2 Answers2