6

I've read Performance difference between MySQL and PostgreSQL for the same schema/queries. Here is a brief retelling of the article:

PostgreSQL tables are heap tables (means no clustered index)... a primary key lookup for a (Postgres) 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 (clustered, physically sorted)... less random disk I/O is required for PK lookup... At the same time, an index scan requires traversing two indexes instead of one (index -> PK index -> table row), meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Which kind of queries are much faster with Postgres than with MySQL InnoDB?

I understand why PK lookup is much better for MySQL. I DON'T understand:

  1. Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?
  2. Why InnoDB sequential scans are slower?

P.S. Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?

closed as primarily opinion-based by Michael - sqlbot, dezso, mustaccio, Max Vernon, datagod Sep 16 '16 at 17:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

15

To avoid flame war, I'll just glance the way each storage work on querying, not really a benchmark. I'll use this table as reference (the code should be slightly modified to run on both RDBMS):

CREATE TABLE employees (
    emp_id int,
    name varchar,
    depto_no int,
    salary decimal,
    CONSTRAINT emp_pk PRIMARY KEY (emp_id);
);
CREATE INDEX emp_depto_idx ON employees (depto_no);

On PostgreSQL there will be 3 structures:

  1. The employees heap, which is basically the table stored sequentially (just like you imagine a table)
  2. The emp_pk index (which is also the primary key), stored as a B-tree index where each element has a pointer to the employee's heap, with the exact page/offset in disk
  3. The emp_depto_idx index, that is like emp_pk, a B-tree with pointers to the heap, except that it does not enforce uniqueness

On MySQL InnoDB, there will be only two:

  1. emp_pk and employees will be stored as one structure, a B-tree ordered by emp_id column, and just keep the value on the other columns as a payload in the leaf nodes.
  2. emp_depto_idx index is a B-tree that on each element it will have the emp_id value referencing that row (not a physical location pointer).

Primary key lookup

why PK lookup is much better for MySQL

I know you know that, but let's make it clear.

When you query it like:

SELECT * FROM employees WHERE emp_id = 10;

On PostgreSQL it can navigate through emp_pk index (one scan on the B-tree index) and then get the page/offset to get referencing row from employees heap table (one direct page/row fetch, not really a scan). So, one scan on the index, and one direct fetch on the heap.

On MySQL it will just navigate through primary key index (one scan on the B-tree index), as all the information is already there, no other lookup is required. So, just one scan on the index.

So, while PostgreSQL needs to do one scan and one fetch, MySQL just do one scan.

Secondary index lookup

Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?

Now, assume this other query:

SELECT * FROM employees WHERE depto_no = 14;

On PostgreSQL, it won't be much different from the other. It will scan emp_depto_idx and then, for each row returned, fetch the value directly from the heap. So, one scan on the index, and a direct fetch on the heap for each row matched.

On MySQL it will scan the emp_depto_idx (one scan on the index), then, for each row returned it will get the referencing emp_id and scan the primary key index. So, one scan on a secondary index, and one scan on the primary index for each row matched.

See the difference? While PostgreSQL will do a scan and then fetch each matching row with a direct pointer, InnoDB will do a similar scan first, then another scan for each matching row. Now, it may be fast enough if department 14 has few employees, but really slow as it gets more employees (of course it will get slower on both RDBMS, but the curve is probably higher with InnoDB).

Full scan

Why InnoDB sequential scans are slower?

Simple answer, because it is not really "sequential"...

Well, let's see the simplest (and certainly slow for all) query:

SELECT * FROM employees;

On PostgreSQL, it may can simple scan the whole employees heap, row by row, in its physical sequential order (no matter the insertion order here, what matters is how the tuples and pages are physically arranged now).

On InnoDB, it needs to traverse the index, which means more random scans (as index pages are not necessarily ordered physically and logically the same way).

If you think about magnetic disks, the difference is quite obvious, it is know that sequential access is way faster than random access. For SSDs it is not necessarily true, although there is still advantages on sequential access, like read-ahead. So in most scenarios, PostgreSQL's full scan will probably faster than InnoDB's, at least for considerable large tables (notice I didn't define what is "large", you must try it out and see where that difference really matters, it may just not matter in many cases). The best, for both RDBMS, is to design your model and your queries to avoid full scans, if possible.

Complex queries

Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?

This is a huge topic, and probably the one that would generate more flame war, so I'll just give you some examples. It is common to say that PostgreSQL is better for complex queries, and it may be a true if you simple think on its query capabilities that are not present in MySQL (not considering performance yet), like:

  • Common Table Expression (CTE)
  • Window Functions
  • LATERAL joins
  • Arrays
  • JSON types, functions and operators
  • etc.

Besides that, there are many difference in the planner and executor of those two. For instance, PostgreSQL can do joins using Nested Loops, Hash Joins and Merge Joins, while MySQL can only do using Nested Loops. Despite that, MySQL has many optimizations in its Nested Loop algorithms, and PostgreSQL has a harder choice on its planner, and sometimes it makes mistake (but so does MySQLs).

Final words

This answer is just a really fast glance on the topic, there are still many things to consider for those two RDBMS when it comes to performance, like index-only scan, vacuum vs undo, parallelism, etc. The truth is that you can't simple say one is faster than the other, it is clear to me (is it to you?) that one can be faster in some environments while the other may be faster in others.

  • @MatheusOl thanks for full disclosure! Really great answer) Yeah, it's absolutely clear for me that there is no way to say one is better than other. Just want to have a clue about which one to choose at the beginning of a project. Actually you never know exactly which queries you'll want to run at the beginning of the project. So what is the better way when you start new project: 1) trying to predict queries and choose Postgres vs MySQL or 2) choose one you like, learn it deeply and try to optimize schema for your specific queries? – VB_ Sep 9 '16 at 16:34
  • @V_B it is really hard to answer your question, but seems that (2) is more like it. In fact, if you are starting in development, you probably should try to work with both and see which one suits you better. You may find which project gets better with each, or to design a project that would run fine with both (very common for OLTP at least). – MatheusOl Sep 9 '16 at 21:50
  • Think of a "scan through a B-Tree" as a "drill down". Further, that is about 3 steps for a million rows, about 6 steps for a trillion rows. Not a big deal. – Rick James Sep 10 '16 at 17:57
  • A factor that I don't think is thoroughly covered in this very good Answer: The impact of caching on minimizing I/O. After all, I/O is the main cost of a query -- perhaps 90%. So, for big tables, the comparison becomes: how likely are blocks (of index or data) to be cached. – Rick James Sep 10 '16 at 18:02

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