pomaly postgres a porovnanie s mysql

[David Celjuska]

Zdarek All!

Dnes som si skusal postgres a nejaky sa mi zdal pomaly tak som
spravil pokus. Vytvoril som tabulku
articles takuto:

\connect - root
CREATE TABLE "article" (
        "id" character varying(10) NOT NULL,
        "obj_kod" character varying(10),
        "popis" character varying(80),
        "net_price" float4,
        "our_price" float4,
        "quantity" int2,
        "group1" character varying(30) DEFAULT 'ine',
        "group2" character varying(30) DEFAULT 'ine',
        "pic1" character varying(15) DEFAULT 'noname.jpg',
        "pic2" character varying(15) DEFAULT 'noname.jpg',
        "alt1" character varying(10),
        "alt2" character varying(10),
        "zisk" int2);
REVOKE ALL on "article" from PUBLIC;
GRANT INSERT,UPDATE,DELETE,SELECT on "article" to GROUP "evid";
CREATE UNIQUE INDEX "article_pkey" on "article" using btree ( "id"
"varchar_ops" );

a napchal som do nej 100 tisic poloziek (riadkov) a prestne to iste som
spravil
s mysql struktura bola:

CREATE TABLE article (
  id varchar(10) DEFAULT '' NOT NULL,
  obj_kod varchar(10),
  popis varchar(80),
  net_price float(10,2),
  our_price float(10,2),
  quantity smallint(6),
  group1 varchar(30) DEFAULT 'ine',
  group2 varchar(30) DEFAULT 'ine',
  pic1 varchar(15) DEFAULT 'noname.jpg',
  pic2 varchar(15) DEFAULT 'noname.jpg',
  alt1 varchar(10),
  alt2 varchar(10),
  zisk smallint(6),
  PRIMARY KEY (id)
);

a tiez s 100 tisic polozkami.

Potom som zadal:
select id from article where id like '2%';

a vysledok bol zaujimavi:

mysql to vratilo za 3.34 sec
a postgres sa s tym hral 9.19 sec

Pred tym ako som tie prikazy zadal tak som restartol aj postgres
aj mysql aby som sa vyhol tomu, ze to budu mat nakesovane.

No a to vobec nechapem kde ma ten postgres problem to spravit
rychlejsie. ID je aj v mysql pouzite ako btree (ine index metody tusim
ani nepozna) a postgres ma tiez btree.

A nehovoriac o tom, ze ked som tie databazy plnil datami (s vlastnym
programom ras s mysql API a potom postgres API - algoritmus bol
rovnaky),
tak mysql som naplnil asi za 30 sec a postgres sa plnil vyse 10 minut.

Tak bud som ja sprosty alebo ten postgres je totalne na hovno.

Pre uplnost:

v mysql: 
+---------+------+---------------+------+---------+------+--------+------------+
| table   | type | possible_keys | key  | key_len | ref  | rows   |
Extra      |
+---------+------+---------------+------+---------+------+--------+------------+
| article | ALL  | PRIMARY       | NULL |    NULL | NULL | 100000 |
where used |
+---------+------+---------------+------+---------+------+--------+------------+
1 row in set (0.07 sec)
v postgres: explain select id from article where id like '2%';
NOTICE:  QUERY PLAN:

Index Scan using article_pkey on article  (cost=1233.33 rows=1
width=120)

EXPLAIN

Mam pocit, ze cena(cost) u postgres-u je strasne vela.

S pozdravom,
      davy!