Why is the count result different from the normal select line count? in Aurora Postgresql

user_11470217 0
Viewed 567

I'm using Aurora's Postgresql, The number of normal count queries does not match the number of rows when searching for records with an asterisk. What is the reason?

The system in question uses AWS Aurora and the 9.6.8 version of Postgresql as the engine. As shown below, the normal search results for Postgresql and the count results do not match.

normal search 
SELECT * FROM samples WHERE date BETWEEN '2019-09-25 00:00:00' AND '2019-09-26 00:00:00';

As a result, 17613 records are returned.

count query 
SELECT COUNT(*) FROM samples WHERE date BETWEEN '2019-09-25 00:00:00' AND '2019-09-26 00:00:00';

17875 is returned as the count query.

This table has multiple primary keys and multiple columns that allow nulls. Why is the number of results different between SELECT * and SELECT COUNT(*)?

By the way, if you specify a table name or primary key, it matches the number of records in normal search.

SELECT COUNT(sample_id) FROM samples WHERE date BETWEEN '2019-09-25 00:00:00' AND '2019-09-26 00:00:00';

or

SELECT COUNT(samples) FROM samples WHERE date BETWEEN '2019-09-25 00:00:00' AND '2019-09-26 00:00:00';

17613 is returned as the count query.

I'm going to cry because my job isn't working. Thank you.

amazon-aurora postgresql sql
2 Answers

Function count(<column_name>) returns the number of non-NULL values. count(*) return a total number of NULL and non-NULL values.

user_34394960

I don't know the root cause, but it was found that this occurred when an Index Only Scan was performed on a read replica of Aurora Postgres.

I looked at each execution plan and it looked like this. An index is used for the date column.

SELECT * 
testdb=> explain analyse select * from samples where date between '2019-09-25 00:00:00' and '2019-09-26 00:00:00';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_samples on samples  (cost=0.43..5852.48 rows=3906 width=627) (actual time=0.014..18.325 rows=17613 loops=1)
   Index Cond: ((date >= '2019-09-25'::date) AND (date <= '2019-09-26'::date))
 Planning time: 1.154 ms
 Execution time: 18.969 ms
(4 rows)

In this case, in order to acquire data as a record, not only an index but actual data is fetched. As a result, 17613 rows are eligible for return.

SELECT COUNT(*) 
testdb=> explain analyse select count(*) from samples where date between '2019-09-25 00:00:00' and '2019-09-26 00:00:00';
                                                                             QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=200.32..200.33 rows=1 width=8) (actual time=19.971..19.972 rows=1 loops=1)
   ->  Index Only Scan using idx_samples on samples  (cost=0.43..190.56 rows=3906 width=0) (actual time=0.022..18.901 rows=17875 loops=1)
         Index Cond: ((date >= '2019-09-25'::date) AND (date <= '2019-09-26'::date))
         Heap Fetches: 59983
 Planning time: 1.125 ms
 Execution time: 19.994 ms
(6 rows)

However, when only the number of records is acquired by counting, the date column added to the condition is the index target, so Index Only Scan is used. As a result, 17875 rows are eligible for return.

This does not occur with Aurora masters, only with read replicas.

I've searched a lot, and I can't find an article about the root cause of the occurrence of unnecessary tuples, or if the vacuum is not done. I also searched Aurora and Postgresql forums, but I couldn't find such an article. The engineer working with me on this event couldn't hide the surprise.

I think that the number of actual data is the correct answer, not the result of Index Only Scan, As a solution, I'm going to check the number of columns that are not indexed by the count, such as SELECT COUNT (sample_id).

It would be helpful if someone shared an article about the cause of this event or that it was such a specification.

user_114702170
Related