Compare queries on PostgreSQL and MySQL -

have nice day!

on windows azure on small vm debian 7.1 installed mysql 5.5.31 , postgresql 9.2.4. insert , select queries make 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) ) (   oids=false ); alter table test   owner 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 tables.

data 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 ~twice faster. 

then make select:

select * test  `fdate` > "2013-07-25" , `fdate` < "2013-08-21"  order `fdate` 

(selects made in loop (1000), , calculate average time.)

mysql: 0.0004650463 сек., 1944 rows pgsql: 0.0139540959 сек., 1944 rows - pgsql 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) , (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 

analyze verbose test (pgsql):

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

the repeated query hit mysql query cache, feature which, believe, not exist in postgresql.

repeat test after disabling query cache, or add sql_no_cache directive query mysql (select sql_no_cache * test...).