Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) - Mailing list pgsql-hackers

Tom,

I was so happy with the problem solved so I decided to play with more joins :-)
Really, queries were processed very quickly but at 14 tables backend died :

COPY t13 FROM STDIN USING DELIMITERS '|';
vacuum analyze;
VACUUM
select t0.a,t1.a as t1,t2.a as t2,t3.a as t3,t4.a as t4,t5.a as t5,t6.a as t6,t7.a as t7,t8.a as t8,t9.a as t9,t10.a as
t10,t11.aas t11,t12.a as t12,t13.a as t13from t0  ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13where t1.a_id = t0.a_t1_id
andt2.a_id=t0.a_t2_id and t3.a_id=t0.a_t3_id and t4.a_id=t0.a_t4_id and t5.a_id=t0.a_t5_id and t6.a_id=t0.a_t6_id and
t7.a_id=t0.a_t7_idand t8.a_id=t0.a_t8_id and t9.a_id=t0.a_t9_id and t10.a_id=t0.a_t10_id and t11.a_id=t0.a_
 
t11_id and t12.a_id=t0.a_t12_id and t13.a_id=t0.a_t13_id ;
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible.  Terminating.


Tom, could you try my script at your machine ?
I attached the script. You need perl to run it.

mkjoindata.pl | psql test
Regards,
    Oleg


On Mon, 17 May 1999, Tom Lane wrote:

> Date: Mon, 17 May 1999 09:44:18 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) 
> 
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > I confirm big join with 11 tables doesn't eats all memory+swap on
> > my Linux box as before and  it runs *forever* :-).  It took already
> > 18 minutes of CPU (P200, 64Mb) ! Will wait. 
> 
> 18 minutes???  It takes barely over a minute on my aging 75MHz HP-PA
> box.  (Practically all of which is planning time, since there are only
> 10 tuples to join... or are you doing this on a realistically sized
> set of tables now?)
> 
> > This query doesn't use (expicitly) GEQO
> 
> > select t0.a,t1.a as t1,t2.a as t2,t3.a as t3,t4.a as t4,t5.a as t5,t6.a as t6,t7.a as t7,t8.a as t8,t9.a as
t9,t10.aas t10
 
> >  from t0  ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10
> >  where t1.a_id = t0.a_t1_id and t2.a_id=t0.a_t2_id and t3.a_id=t0.a_t3_id and t4.a_id=t0.a_t4_id and
t5.a_id=t0.a_t5_idand t6.a_id=t0.a_t6_id and t7.a_id=t0.a_t7_id and t8.a_id=t0.a_t8_id and t9.a_id=t0.a_t9_id and
t10.a_id=t0.a_t10_id;
 
> 
> No, but since there are 11 tables mentioned, it will be sent to the GEQO
> optimizer anyway with the default GEQO threshold of 11...
> 
>             regards, tom lane
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
#!/usr/local/bin/perl -w
#use strict;

# Number of tables - classificators
my $ntables  = 14;
# Number of joins, must be <= $ntables !
my $njoins   = 13;
# dimensions of tables
my @nitems   = ( 200, 200, 10, 10, 10, 10, 10,10,10, 10,10);


die ("njoins ($njoins) must be >= ntables ($ntables) !")  if ( $njoins > $ntables);
my $tables = ($njoins < $ntables ) ? $njoins : $ntables;
my $SQL = '';
# classificators
for ( $i = 0; $i <= $tables; $i++ ) { my $table = 't'. $i; $SQL .= qq^drop table $table;\ncreate table $table ( a_id
int4NOT NULL, a varchar(10)^;   if ( $i == 0 ) { # main table      for ( $k = 1; $k <= $ntables; $k++ ) {         $SQL
.=', a_t'.$k.'_id int4';      }   }  $SQL .= qq^);\ncreate index a_id_$table on $table (a_id);\n^;  if ( $i == 0 ) { #
maintable - create add. indices      for ( $k = 1; $k <= $ntables; $k++ ) {         my $table = 't'.$k;         $SQL .=
'createindex a_'.$table.'_id_t0 on t0 (a_'.$table.'_id);';         $SQL .= "\n";      }   } $SQL .= qq^COPY $table FROM
STDINUSING DELIMITERS '|';\n^; srand($i); for ( $j = 1; $j <= $nitems[$i]; $j++ ) {     my @cols = ();
 
#    push @cols, (int(rand($nitems[$i])),'a'.$table.$j);   push @cols, ($j,'a'.$table);   if ( $i == 0 ) { # main table
-add cols      for ( $k = 1; $k <= $ntables; $k++ ) {
 
#          push @cols, int(rand($nitems[$k]));         push @cols, $j;      }   }   $SQL .= join ('|',@cols)."\n";    }
$SQL.= qq^\\.\n^;
 
}

# vacuum analyze
$SQL .= qq^vacuum analyze;\n^;
print "$SQL\n";

# Query with joins
my $SELECT = '';
# Try GEQO
#$SELECT .= qq^set GEQO to 'ON=$njoins';^;
#$SELECT .= 'explain ';
$SELECT .= 'select t0.a';
for ( $i = 1; $i <= $njoins; $i++ ) { $SELECT .= ',t'.$i.'.a as t'.$i;
}
$SELECT .= "\n".' from t0  ';
for ( $i = 1; $i <= $njoins; $i++ ) { $SELECT .= ',t'.$i;
}
$SELECT .= "\n".' where t1.a_id = t0.a_t1_id ';
for ( $i = 2; $i <= $njoins; $i++ ) { $SELECT .= 'and t'.$i.'.a_id=t0.a_t'.$i.'_id ';
}
$SELECT .= ';';

print $SELECT,"\n";