Compare queries on PostgreSQL and MySQL

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