SQLAlchemy Faster Than Psycopg2. What's Wrong with My Benchmark?

Question

First, I create a table with 5 columns:

CREATE TABLE table1
(
  a integer,
  b character varying,
  c character varying,
  d numeric,
  e numeric
)

Second, I add 100k rows to a table

https://gist.github.com/ericsalim/1d12628826195b52c5d282c2326f5e00

Third, I select all the rows using Psycopg2 and SQLAlchemy

• Using Psycopg2: https://gist.github.com/ericsalim/f45d0f0d4babf0e59708c75804eb78c4
• Using SQLAlchemy: https://gist.github.com/ericsalim/c8df8adeb9ec49fa8cb1b5c5e8d811f8

The result for Psycopg2

• query time: 2839.46800232
• conversion time: 792.379140854
• res size: 400036
• rowlist size: 412232

The result for SQLAlchemy:

• query time: 2401.74889565
• conversion time: 377.213001251
• res size: 412232
• rowlist size: 412232

How is it possible that SQLAlchemy, which is an ORM layer on top Psycopg2 is faster than Psycopg2 itself? Is there something wrong with my code?

Answer 1

It's the overhead of the DictCursor. Switching it to use a standard cursor and integer indices results in psycopg2 outperforming. I imagine SQLAlchemy uses a different, more efficient method for supporting string indexes. But other than academic curiosity, I would not worry about the performance difference between the two - focus on which is more productive for you. In most real-world scenarios, you will be I/O bound, and the difference is small enough to not matter.