Compare queries on PostgreSQL and MySQL

Question

Have a nice day!

On Windows Azure On Small VM with Debian 7.1 I installed MySQL 5.5.31 and PostgreSQL 9.2.4. Insert and Select queries will make from php via pdo.

Creating table:

MySQL:

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fdate` datetime NOT NULL,
  `ftext` varchar(1000) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `ix_date` (`fdate`),
  KEY `ix_text` (`ftext`(255))
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

PgSQL:

CREATE TABLE test
(
  fdate timestamp without time zone,
  ftext character varying(1000),
  id bigserial NOT NULL,
  CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test
  OWNER TO postgres;

CREATE INDEX ix_date
  ON test
  USING btree
  (fdate);

CREATE INDEX ix_text
  ON test
  USING btree
  (ftext COLLATE pg_catalog."default");

Make Inserts into tables.

Data is like this:

152 2013-07-25 00:01:47 51e811712cfd6
100151  2013-07-25 00:28:25 51e825bfea275
101151  2013-07-25 00:29:26 51e825fcc5d94
153 2013-07-25 01:01:47 51e8117134c14
100152  2013-07-25 01:28:25 51e825bff1eb7
101152  2013-07-25 01:29:26 51e825fccd9e7
154 2013-07-25 02:01:47 51e811713d80d
100153  2013-07-25 02:28:25 51e825c0077c7
101153  2013-07-25 02:29:26 51e825fcd561a
155 2013-07-25 03:01:47 51e811716ffb2
100154  2013-07-25 03:28:25 51e825c013225
101154  2013-07-25 03:29:26 51e825fcdd243
156 2013-07-25 04:01:47 51e8117179af0
100155  2013-07-25 04:28:25 51e825c01cd74
101155  2013-07-25 04:29:26 51e825fce3f1c

In each table inserted 102 000 rows.

Average time of insert:

MySQL: 0.0328167504 сек.
PgSQL: 0.0183281872 сек.
- PgSQL is ~twice faster.

Then I make select:

select * from test 
where `fdate` > "2013-07-25" and `fdate` < "2013-08-21" 
order by `fdate`

(SELECTs made in FOR loop (1000), and then calculate average time.)

MySQL: 0.0004650463 сек., 1944 rows
PgSQL: 0.0139540959 сек., 1944 rows
- PgSQL by 30! times more slowly.

Why?

PgSQL EXPLAIN (ANALYZE, BUFFERS):

"Index Scan using ix_date on test  (cost=0.00..36.86 rows=780 width=30) (actual time=0.018..4.672 rows=1944 loops=1)"
"  Index Cond: ((fdate > '2013-07-25 00:00:00'::timestamp without time zone) AND (fdate < '2013-08-21 00:00:00'::timestamp without time zone))"
"  Buffers: shared hit=1954"
"Total runtime: 7.594 ms"

MySQL EXPLAIN:

1   SIMPLE  test    range   ix_date ix_date 8       1942    Using where

analyze VERBOSE test (PgSQL):

INFO:  analyzing "public.test"
INFO:  "test": scanned 750 of 750 pages, containing 102000 live rows and 0 dead rows; 30000 rows in sample, 102000 estimated total rows

Show the execution plans for both DBMS. Did you analyze the Postgres tables? Btw: the select as shown will not run on Postgres. Also: did you tune the Postgres server in any way? The default settings are pretty conservative. More details on how to ask performance related questions are here: wiki.postgresql.org/wiki/Slow_Query_Questions — user330315
– user330315, Commented Jul 17, 2013 at 16:18
Also, the two databases are reporting different numbers of rows which makes the test a bit iffy. And make sure you are measuring time to return all the rows on both, not time to return the first row with MySQL. Oh, and please don't call columns things like "date" or "int" or "select" - even when it doesn't confuse the RDBMS it confuses poor human readers. — Richard Huxton
– Richard Huxton, Commented Jul 17, 2013 at 16:37
Did not tune. PgSQL explain: "Sort (cost=1241.28..1249.31 rows=3210 width=26)" " Sort Key: date" " -> Bitmap Heap Scan on test (cost=101.18..1054.33 rows=3210 width=26)" " Recheck Cond: ((date > '2013-07-25'::date) AND (date < '2013-08-21'::date))" " -> Bitmap Index Scan on ix_date (cost=0.00..100.38 rows=3210 width=0)" " Index Cond: ((date > '2013-07-25'::date) AND (date < '2013-08-21'::date))" — programmister
– programmister, Commented Jul 17, 2013 at 17:22
Quick tip for working with MySQL and another DB together: Consider running MySQL in ANSI mode with the STRICT options set. It'll let you use ANSI standard quoting, eg "identifier" and 'literal', instead of MySQL's funky quoting. — Craig Ringer
– Craig Ringer, Commented Jul 18, 2013 at 1:31

RandomSeed · Accepted Answer · 2013-07-25 21:56:15Z

2

The repeated query hit the MySQL query cache, a feature which, I believe, does not exist in PostgreSQL.

Repeat the test after disabling the query cache, or add the SQL_NO_CACHE directive to your query to MySQL (SELECT SQL_NO_CACHE * FROM test...).

edited Jul 25, 2013 at 21:56

answered Jul 25, 2013 at 21:50

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IMSoP Over a year ago

Another way to rule out any caching of this kind if you don't know the details of the system you are benchmarking is to deliberately vary your queries/operations in an unpredictable pattern rather than repeating identical operations.

Collectives™ on Stack Overflow

Compare queries on PostgreSQL and MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related