SQL command speed

I am running PostgreSQL 7.0 on Red Hat Linux 6.2. I am fairly new to
using PostgreSQL. I am in the process of comparing performance with an
Oracle data base. I have converted and populated several tables from
Oracle to PostgreSQL.

To test the speed of the data bases, I wrote the following script using
the PERL DBI:

--- Start Script 1 ---
#!/bin/perl
use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=pbi", "ntm", "", { RaiseError =>
1, AutoCommit => 0 })
or die "Can't connect to PGSQL";

$sql = "SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY
HH24:MI')
FROM notam_details
WHERE
item_a = 'EGKB' OR
item_a = 'EGDM' OR
item_a = 'EGHH' OR
item_a = 'EGGD' OR
item_a = 'EGVN' OR
item_a = 'EGFF' OR
item_a = 'EGDC' OR
item_a = 'EGTC' OR
item_a = 'EGDR' OR
item_a = 'EGTE' OR
item_a = 'EGLF' OR
item_a = 'EGTG' OR
item_a = 'EGBJ' OR
item_a = 'EGLC' OR
item_a = 'EGKK' OR
item_a = 'EGLL' OR
item_a = 'EGSS' OR
item_a = 'EGGW' OR
item_a = 'EGMD' OR
item_a = 'EGDL' OR
item_a = 'EGUM' OR
item_a = 'EGHD' OR
item_a = 'EGHE' OR
item_a = 'EGKA' OR
item_a = 'EGHI' OR
item_a = 'EGMC' OR
item_a = 'EGDG' OR
item_a = 'EGFH' OR
item_a = 'EGDY' OR
item_a = 'EGJA' OR
item_a = 'EGJB' OR
item_a = 'EGJJ'";

$sth = $dbh->prepare( $sql);
$sth->execute();
$result = $sth->fetchall_arrayref();
foreach (@{$result})
{
($id, $date) = @$_;
print "$id:$date\n";
}
--- End Script 1 ---

When I ran it it took 12 seconds with PostgreSQL and 1 second in Oracle.

I then went through several steps using vacuum, building indexes, etc,
but I did not see much of a performance improvement. Then I used
explain, and noticed that the query was not using the indexes I created.
I did some experimentation, and if the WHERE clause had one or two items
it would use the index; more and it would not.

So I rewrote the script to do multiple small select queries instead of
one big select query. The new script looked like this:

--- Start Script 2 ---
#!/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=pbi", "ntm", "",
{ RaiseError => 1, AutoCommit => 0 })
or die "Can't connect to PGSQL";

$sql = "SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY HH24:MI')

FROM notam_details
WHERE item_a = ?";

$sth = $dbh->prepare( $sql);

@stations = (EGKB, EGDM, EGHH, EGGD, EGVN, EGFF, EGDC, EGTC, EGDR, EGTE,
EGLF, EGTG, EGBJ, EGLC, EGKK, EGLL, EGSS, EGGW, EGMD, EGDL, EGUM, EGHD,
EGHE, EGKA, EGHI, EGMC, EGDG, EGFH, EGDY, EGJA, EGJB, EGJJ);
foreach (@stations)
{
$sth->bind_param( 1, $_);
$sth->execute();
$result = $sth->fetchall_arrayref();
foreach $s (@{$result})
{
($id, $date) = @$s;
print "$id:$date\n";
}
}
--- End Script 2 ---

The result was the execution time of the script dropped to 1 second
using PostgreSQL!

At first I thought it was a feature of the PERL DBI, but I ran the same
queries using psql, and I got similar results. I also ran some timing
checks of the PERL code and 99% of the execution time of the Script 1 is
being spent in the "execute" statement.

I have looked through the documentation of PostgreSQL, and I can find no
explanation for this. I was curious if this is a know issue and thus is
the proper way to create SELECT statements in PostgreSQL?

Kate Collins

BTW, Script 2 takes a little longer to run on the Oracle system, circa
1.3 seconds.

--
=================================================
Katherine (Kate) L. Collins
Senior Software Engineer/Meteorologist
Weather Services International (WSI Corporation)
4 Federal Street
Billerica, MA 01821
EMAIL: klcollins(at)wsicorp(dot)com
PHONE: (978) 670-5110
FAX: (978) 670-5100
http://www.intellicast.com