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 ($i<500) {
     $vector = $dbh->selectcol_arrayref("SELECT induction FROM c$i
order by orf");
     for (0..$#{$vector}) {
          $matrix[$_]->[$i] = $vector[$_];
     }
     $i++;
}




Thanks,
Evan