Re: performance tuning or real bug ?

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%'