On 14 Jun 2001, denis wrote: > 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%' ? You're probably running into problems with the IN, which tends to be slow (see the FAQ for workarounds). Also, why are you using an in, isn't the above equivalent to: select sum(montant) from names where nom like '%12%'; -- or '%1%' ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
