MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

Question

I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong.

I have a table with ~200M records. MySQL table definition:

CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  t2_id int(11) NOT NULL,
....  
  PRIMARY KEY (id),
  KEY index_t2 (t2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Request (returns ~30M):

SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

runs:

25,797ms MySQL (v5.7.11)

1,222,168ms PostgeSQL (v9.5)

Explain:

MySQL:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: index_t2
          key: index_t2
      key_len: 4
          ref: const
         rows: 59438630
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

PostgreSQL

Aggregate  (cost=4469365.02..4469365.03 rows=1 width=0)
 ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=0)
       Recheck Cond: (t2_id = 7)
       ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0)
             Index Cond: (t2_id = 7)

Server: AWS RDS (db.r3.xlarge) vCPU:4 Memory:30Gb

Updated (2016-09-20):

> explain (analyze, buffers) SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

QUERY PLAN                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4469365.02..4469365.03 rows=1 width=4) (actual time=1213456.539..1213456.539 rows=1 loops=1)
   Buffers: shared read=2734808
   ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=4) (actual time=64015.828..1205542.421 rows=31383566 loops=1)
         Recheck Cond: (t2_id = 7)
         Rows Removed by Index Recheck: 108582028
         Heap Blocks: exact=19929 lossy=2606242
         Buffers: shared read=2734808
         ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=64009.598..64009.598 rows=31383566 loops=1)
               Index Cond: (t2_id = 7)
               Buffers: shared read=108637
 Planning time: 0.080 ms
 Execution time: 1213456.891 ms
(12 rows)

Time: 1213484.579 ms

Updated (2016-09-21):

> explain (analyze, buffers) SELECT t2_id FROM t1 WHERE t2_id = 7;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=114) (actual time=59954.834..1234070.436 rows=31383566 loops=1)
   Recheck Cond: (t2_id = 7)
   Rows Removed by Index Recheck: 108582028
   Heap Blocks: exact=19929 lossy=2606242
   Buffers: shared hit=4824 read=2729984
   ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=59948.598..59948.598 rows=31383566 loops=1)
         Index Cond: (t2_id = 7)
         Buffers: shared hit=4824 read=103813
 Planning time: 0.086 ms
 Execution time: 1239826.408 ms
(10 rows)

Time: 1239827.053 ms

Answer 1

The way that both RDBMS do the count differs. In InnoDB we have the following behaviour by default:

To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool.

For Postgres, you may want to try to see if an index-only scan (which is closer to the InnoDB behaviour) can help you on this. More info here. Due the amount of rows and the bad cardinality of that value (almost 15% of the table according stats), I can't warranty that it will work, but you can try:

SELECT COUNT(t2_id) FROM t1 WHERE t2_id = 7; 

Answer 2

Adressing Postgres.
Maybe an estimate is good enough. Any count is an estimate after all and may be outdated the moment you see it. A fresh count just minimizes the time window. For read-only tables, statistics in the system catalog are just as good. Either way, much faster.

There are basic statistics in pg_class and much more in pg_statistic (or the human-readable view pg_stats based on it).

To get the row count for your selection, create a partial index:

CREATE INDEX t1_t2_id_7_idx ON t1((1)) WHERE t2_id = 7;

A row for the partial index incl. a row count is entered in pg_class immediately, including a row count (or estimate for you don't even have to run ANALYZE on the table (or wait for autovacuum to kick in). Now you can get a count estimate at close to no cost:

SELECT reltuples::bigint AS count_estimate
FROM   pg_class
WHERE  oid = 't1_t2_id_7_idx'::regclass;

The manual about pg_class.reltuples:

Number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.

More in these related answers:

• Get count estimates from pg_class.reltuples for given conditions
• Fast way to discover the row count of a table
• Index that is not used, yet influences query
• PostgreSQL not using index during count(*)

Answer 3

Some of this may be due to the "Multi Version Concurrency Control" (MVCC) Postgres uses to manage changes within active transactions. It has to check each row in the table to make sure that it should be visible to the current transaction. This may seem a bit backwards, but in circumstances where it would make a difference mySQL may instead simply lock the entire table which could have a significant impact on concurrency and therefor performance for many workloads.

There are of course methods that could be employed to minimise the impact this has on SELECT COUNT(*) or similar queries, but these methods increase the work that needs to be done in other processes so are likely to be considered bad optimisations (optimising for SELECT COUNT(*) at the expense of extra CPU load, and in some cases IO, for every single row insert or update operation).