pomaly postgres a porovnanie s mysql
Jan Varga
varga na utcru.sk
Neděle Březen 19 20:28:19 CET 2000
David Celjuska wrote:
> 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.
>
skuste "man postgres" a option "-F"
tento option niekedy urychli postgres aj niekolko nasobne
tak isto dost pomoze zvacsit zdielany bufer cez option "-B"
>
> 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.
po vacsom naplnany databazy sa odporuca "rebuildnut" indexy a takisto pouzit prikaz
"vacuum"
o tychto problemoch sa uz vela diskutovalo na mnohych konferenciach
skuste napr. archiv konferencie pgsql-general na http://www.postgresql.org
S pozdravom
J.Varga
Další informace o konferenci Linux