Re: Optimizing Postgres selects

[wsheldah]

Forgot to mention maintaining an index on the orf column in each table to speed
up the order by, and running VACUUM ANALYZE on a table any time it changes
substantially, or at appropriate intervals.  Is it possible to make orf an
integer if it isn't already?  Remember that the index will slow down inserts and
updates, so weigh the tradeoff.




Evan Zane Macosko [EMAIL PROTECTED] on 08/13/2001
05:04:50 PM

To:   [EMAIL PROTECTED]
cc:(bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  Optimizing Postgres selects


Hi everyone,


I am use Perl:DBI to interface to a Postgres database that contains many
tables with two columns and about 6000 rows.  Inside my perl program, i
need to select one column from each of these 500 tables--as many as 500 at
once--and the way  I'm doing it, it seems to take a lot of time.  I was
wondering if people  might be able to offer some alternate code.  Below is
the appropriate snippet from the program I'm writing:


my $i = 1;
my $vector;
my @matrix;
while ($i500) {
 $vector = $dbh-selectcol_arrayref(SELECT induction FROM c$i
order by orf);
 for (0..$#{$vector}) {
  $matrix[$_]-[$i] = $vector[$_];
 }
 $i++;
}




Thanks,
Evan

Re: Optimizing Postgres selects

[Alex Pilosov]

Your problem is your database structure. Instead of putting things into
500 tables, you should merge them into 1 table. 

This is not a perl question, its a database design question.

-alex
On Mon, 13 Aug 2001, Evan Zane Macosko wrote:

 Hi everyone,
 
 
 I am use Perl:DBI to interface to a Postgres database that contains many
 tables with two columns and about 6000 rows.  Inside my perl program, i
 need to select one column from each of these 500 tables--as many as 500 at
 once--and the way  I'm doing it, it seems to take a lot of time.  I was
 wondering if people  might be able to offer some alternate code.  Below is
 the appropriate snippet from the program I'm writing:
 
 
 my $i = 1;
 my $vector;
 my @matrix;
 while ($i500) {
   $vector = $dbh-selectcol_arrayref(SELECT induction FROM c$i
 order by orf);
   for (0..$#{$vector}) {
   $matrix[$_]-[$i] = $vector[$_];
   }
   $i++;
 }
 
 
 
 
 Thanks,
 Evan