performance tuning or real bug ?

From: g(dot)denis(at)gmx(dot)fr (denis)
To: pgsql-general(at)postgresql(dot)org
Subject: performance tuning or real bug ?
Date: 2001-06-14 12:07:47
Message-ID: dcb6f478.0106140407.5e8c5594@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message yaplab 2001-06-14 12:44:38 Window 2000 Port??
Previous Message Einar Karttunen 2001-06-14 12:06:23 Re: OIDs overflow