performance tuning or real bug ?
От | g.denis@gmx.fr (denis) |
---|---|
Тема | performance tuning or real bug ? |
Дата | |
Msg-id | dcb6f478.0106140407.5e8c5594@posting.google.com обсуждение исходный текст |
Ответы |
Re: performance tuning or real bug ?
|
Список | pgsql-general |
I use a linux/mandrake 7.2 on PIII 350 when doing ****************1 - create an initialisation file i=0; loadfile="/usr/local/pgsql/param/loadfile" rm -fr $loadfile ; #creating a file with 1500 records while [ $i -lt 1500 ] ; do i=`expr $i + 1`; mod=`expr $i % 10`; #creating a field amount with different numbers mont=`expr $mod \* 18 + $i `; echo $i"|"nom$i"|"prenom$i"|"$mont>>$loadfile; if [ $mod -eq 0 ] ; then echo " $i lignes created " ; fi done echo "Load file done " ; ************* 2 - creating and populating database psql essai <<++ create table names ( id integer, nom char(40) , prenom char(20), montant decimal ); copy names from '/usr/local/pgsql/param/loadfile' delimiters '|'; create unique index id_names on names(id); create index nom_names on names(nom); ++ ************** 3 - executing a select psql essai <<++ select sum (montant) from names where nom in (select nom from names where nom like '%1%' ); ++ *************** 4 - checking results command : time sql.sh sum --------------- 787494.000000 (1 row) 0.01user 0.01system 0:12.08elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (421major+110minor)pagefaults 0swaps it took 12 seconds (I did the same with an informix SE database and the result is (sum) 787494.00 1 row(s) retrieved. real 0m0.62s user 0m0.03s sys 0m0.03s ****************** 5 - Other tests if I change the like clause and execute : psql essai <<++ select sum (montant) from names where nom in (select nom from names where nom like '%12%' ); ++ the result is sum --------------- 157132.000000 (1 row) 0.02user 0.01system 0:00.56elapsed 5%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (422major+109minor)pagefaults 0swaps with informix it's nearly the same In both cases I also did the same whith dropping the indexes and the results are quite the same. can someone explain me why there's a so huge difference between LIKE '%1%' and LIKE '%12%' ? thanks for all denis
В списке pgsql-general по дате отправления: