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