I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.

I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests comparing with MySQL too. We observe that MySQL is twice as fast as PostgreSQL.

I did a tangible performance test:

  • Same columns in table with equivalent column datatypes.
  • Same number of rows.
  • Same indexes in both (primary key included).
  • The CPU load are idle and Postgres machine it's significantly better.
  • And the same query (obviously).

What am I doing wrong?

P.S: I read many "howtos" on performance tuning for database engines.
P.S(2): We're using InnoDB (one file per table) on the MySQL database.

Hi Mat!

I did the three common select (and hardest) queries.

The question about disk, certainly it's not the same; In Postgres it's a SSD (almost three time fastest).

MySQL cache data:

| Variable_name                | Value                |
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |

I don't know how to view this in PostgreSQL.

Thanks in advance.

  • Sorry for my English Commented Apr 30, 2013 at 14:52
  • (Your English is fine.) Did you do load tests, or just individual queries? Could you show the database settings you used (especially things like cache sizes)? (Same disks in both cases I presume?)
    – Mat
    Commented Apr 30, 2013 at 15:03
  • 1
    Can you post the query and the Postgres execution plan using explain analyze. To make it easier to read, you can upload the plan to explain.depesz.com
    – user1822
    Commented Apr 30, 2013 at 16:17
  • 1
    If Postgres is running on a SSD you almost certainly have to tune postgresql.conf
    – user1822
    Commented Apr 30, 2013 at 16:45
  • 1
    @JavierValencia: if you were able to fix the problem, please add an answer describing what you did so that others can learn from that. You can also accept your own answer in order to mark this question as solved
    – user1822
    Commented Oct 1, 2013 at 9:13

1 Answer 1


MySQL and PostgreSQL are quite difference performance-wise. InnoDB and PostgreSQL tables are optimized for different sorts of queries. Understanding these differences is important to understanding how to get good performance out of either.

As an example, let's look at the most obvious difference.

PostgreSQL vs MySQL/InnoDB Table Structure and What This Means for Performance

In general, on complex work-loads, PostgreSQL will be faster, but on simple primary key lookups MySQL with InnoDB will be faster.

PostgreSQL tables are heap tables. There is no option to build a table which is not a heap table. The cluster command simply rewrites the heap ordered by a specified index. Indexes then provide heap locations for tuples with various values. Indexes cannot be traversed in physical order, only logical order so they have a lot of random disk I/O while reading a table sequentially usually means a lot of sequential disk I/O, since you can read a table in physical order. Sequential disk I/O gets to use read-ahead cache and some other OS-level optimization.

What this means is that if you need a significant portion of records or over a few pages, it is usually faster to just read the pages from disk. On the other hand, a primary key lookup for a table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record. This means a number of pieces of random disk I/O.

InnoDB uses a different approach. With InnoDB, the table is a b-tree index with the actual data in the index payload. This means that a primary key lookup already gets to pull the data from the leaf page, and so less random disk I/O is required for this. At the same time, an index scan requires traversing two indexes instead of one, meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Getting Diagnoses in PostgreSQL

I think you want to use something like:

 EXPLAIN (analyse, buffers, verbose)

That will give you the query plan, initial estimates, actual times, buffer usage, and much more.

  • 4
    +1 for EXPLAIN (analyse, buffers, verbose)
    – karmakaze
    Commented Mar 18, 2015 at 14:56
  • @ChrisTravers thanks for a great answer! You said: "...(InnoDB's) sequential scans are slower". Could you pls explain what do you mean by sequential scans in this context?
    – VB_
    Commented Sep 6, 2016 at 13:33
  • thanks. I will modify the answer. "Sequential" scans in InnoDB are in index-logical order so you have more random I/O and no help from read-ahead caching. Commented Sep 8, 2016 at 7:00
  • Thanks for nice answer. For anyone curious about postgres' internal, I recommend this post: interdb.jp/pg/pgsql01.html Explain how Postgres store data as heap table.
    – hqt
    Commented Feb 3, 2019 at 22:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.