Have a nice day!
On Windows Azure, on a small Debian 7.1 virtual machine, I installed MySQL 5.5.31 and PostgreSQL 9.2.4. Insertion and selection of requests will be performed with php via pdo.
Creating a 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.
The data is as follows:
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
Each table has 102,000 rows inserted.
Average insertion time:
MySQL: 0.0328167504 . PgSQL: 0.0183281872 . - PgSQL is ~twice faster.
Then I do select:
select * from test where `fdate` > "2013-07-25" and `fdate` < "2013-08-21" order by `fdate`
(SELECT made in a FOR (1000) loop, and then calculate the 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 the 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